五、保存數據到Excel、sqlite（爬蟲及數據可視化）
- 1，保存數據到excel
- 1.1 保存九九乘法表到excel
- （1）代碼testXwlt.py
- （2）excel保存結果
- 1.2 爬取電影詳情并保存到excel
- （1）代碼spider.py
- （3）excel保存結果
- 2，保存數據到sqlite
- 2.1 sqlite數據庫
- 2.2 創建表
- 2.3 插入數據
- 2.4 查詢數據
- 2.5 保存數據到數據庫db文件
- 2.6 完整代碼spider.py
1，保存數據到excel
1.1 保存九九乘法表到excel
（1）代碼testXwlt.py
#-*- codeing = utf-8 -*-
#@Time : 2020/11/23 15:35
#@Author : 招財進寶
#@File : testXwlt.py
#@Software: PyCharmimport xlwt'''
workbook = xlwt.Workbook(encoding="utf-8") #創建workbook對象（可以理解為一個文件）
worksheet = workbook.add_sheet('sheet1') #創建工作表，可理解為文件中的表單
worksheet.write(0,0,"hello") #寫入數據（寫到的是內存中），第一個參數“行”，第二個“列”，第三個參數“內容”
workbook.save('student.xls') #保存數據表到硬盤
'''workbook = xlwt.Workbook(encoding="utf-8") #創建workbook對象（可以理解為一個文件）
worksheet = workbook.add_sheet('sheet1') #創建工作表，可理解為文件中的表單for i in range(0,9):for j in range(0,i+1):worksheet.write(i, j, "%d*%d=%d"%(i+1,j+1,(i+1)*(j+1)))workbook.save('student.xls') #保存數據表到硬盤
（2）excel保存結果
1.2 爬取電影詳情并保存到excel
（1）代碼spider.py
以下為完整代碼
#-*- codeing = utf-8 -*-
#@Time : 2020/11/20 11:08
#@Author : 招財進寶
#@File : spider.py
#@Software: PyCharmfrom bs4 import BeautifulSoup #網頁解析，獲取數據
import re #正則表達式，進行文字匹配
import urllib.request,urllib.error #指定url，獲取網頁數據
import xlwt #進行Excel操作
import sqlite3 #進行SQLite操作def main():baseurl = "https://movie.douban.com/top250?start="#1.爬取網頁datalist = getData(baseurl)savapath = "豆瓣電影TOP250.xls"#3.保存數據savaData(datalist,savapath)#askURL("https://movie.douban.com/top250?start=0")#影片詳情鏈接的規則
findLink = re.compile(r'a href="(.*?)">') #生成正則表達式對象，表示規則（字符串的模式）,用來匹配所有的鏈接
#影片 圖片的鏈接
findImgSrc = re.compile(r'<img.*src="(.*?)"',re.S) #re.S忽略里面的換行符，讓換行符包含在字符中
#影片片名
findTitle = re.compile(r'<span class="title">(.*)</span>')
#影片的評分
findRating = re.compile(r'<span class="rating_num" property="v:average">(.*)</span>')
#找到評價人數
findJudge = re.compile(r'<span>(\d*)人評價</span>')
#找到概況
findIng = re.compile(r'<span class="inq">(.*)</span>')
#找到影片的相關內容
findBd = re.compile(r'<p class="">(.*?)</p>',re.S) #re.S忽略里面的換行符，讓換行符包含在字符中#爬取網頁
def getData(baseurl):datalist=[]for i in range(0,10): #此處是爬取10頁是數據，每頁有25個電影數據（（0,10）左閉，右開）url = baseurl + str(i*25) #每一頁的url后的起始位置=頁數*25html = askURL(url) #保存獲取到的網頁源碼# 2.逐一解析數據（每個網頁解析一下）soup = BeautifulSoup(html,"html.parser") #使用html解析器html.parser解析html# find_all()查找符合要求的字符串，形成列表for item in soup.find_all('div',class_="item"): #class是個類別需要加下劃線，將及時div又是class_="item"的標簽找到#print(item) #測試：查看電影item全部信息#print(type(item))data=[] #保存一部電影的所有信息item = str(item) #str將item變成字符串#print(item)#print(type(item))#break #用來測試第一條item#接下來可以使用正則表達式對字符串進行解析了#影片詳情的鏈接link = re.findall(findLink,item)[0] #re庫用來通過正則表達式查找指定的字符串,獲取兩個相同的鏈接中的第一個#print(link)data.append(link) #添加鏈接imgSrc = re.findall(findImgSrc,item)[0]data.append(imgSrc) #添加圖片titles = re.findall(findTitle,item) #片名可能只有一個中文名，沒有外文名if(len(titles)==2) :ctitle =titles[0] #中文名的標題data.append(ctitle)otitle = titles[1].replace("/","") #去掉無關的符號data.append(otitle) #添加外國名else:data.append(titles[0]) #將第一個中文名填入data.append(' ') #留空，用于占據外國名的位置，防止位置不對稱rating = re.findall(findRating,item)[0]data.append(rating) #添加評分judgeNum = re.findall(findJudge,item)[0]data.append(judgeNum) #添加評價人數inq = re.findall(findIng,item)#（不一定每個電影都有概述）if len(inq) !=0:inq=inq[0].replace("。","") #去掉句號data.append(inq) #添加概述else:data.append(" ") #留空bd = re.findall(findBd,item)[0]bd = re.sub('<br(\s+)?/>(\s+)?'," ",bd) #去掉<br/>bd = re.sub('/'," ",bd) #替換/data.append(bd.strip()) #去掉前后的空格datalist.append(data) #把處理好的一部電影信息放入datalist#print(datalist)return datalist #返回數據列表#得到指定一個url的網頁內容
def askURL(url):#head作用是為了讓對方自己是瀏覽器，模擬瀏覽器頭部信息，向豆瓣服務器發送消息#head信息一定不要寫錯，否則會返回否碼為418，對方不認為我們是瀏覽器head = {"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36"} #如果信息不多的話，可以使用鍵值對的方式，若很多的話可以使用列表的方式head=[]#用戶代理。表示告訴豆瓣服務器。我們是什么類型的機器。瀏覽器（本質上是告訴瀏覽器。我們可以接收什么水平的文件內容）#發送消息使用下方方式request = urllib.request.Request(url=url, headers=head) # 構建的是請求對象,使用Request()封裝了請求對象,此對象包含url和head頭部信息#可以攜帶頭部信息訪問url,try:response = urllib.request.urlopen(request) #發送請求對象，返回一個response對象，此對象包含我們的網頁信息html = response.read().decode("utf-8") #將讀取的html界面讀取出來#print(html) #打印獲取的網頁except urllib.error.URLError as e: #訪問時可能會遇到404遇到一些瀏覽器內部錯誤，如500等if hasattr(e,"code"): #將里面的如404等code打印出來print(e.code)if hasattr(e, "reason"): #將產生的錯誤的原因打印出來print(e.reason)return html#保存數據
def savaData(datalist,savapath):book = xlwt.Workbook(encoding="utf-8",style_compression=0) # 創建workbook對象（可以理解為一個文件）,style_compression樣式壓縮效果sheet = book.add_sheet('豆瓣電影Top250',cell_overwrite_ok=True) # 創建工作表，可理解為文件中的表單,cell_overwrite_ok是否覆蓋掉以前的內容col = ('電影詳情鏈接',"圖片鏈接","影片中文名","影片外國名","評分","評價數","概況","相關信息")for i in range(0,8):sheet.write(0,i,col[i]) #列名for i in range(0,250):print("第%d條"%(i+1))data = datalist[i]for j in range(0,8):sheet.write(i+1,j,data[j]) #數據book.save(savapath) # 保存數據表到硬盤if __name__ == "__main__": #當程序執行時#調用函數main()print("爬取完畢！")
（3）excel保存結果
以下為爬蟲的Excel保存版本的結果
2，保存數據到sqlite
2.1 sqlite數據庫
一定注意下方的可能標紅，要選擇下載驅動文件
確定后如下
右側的數據庫管理窗口圖形平臺，不弱于任何一個圖形化的數據庫管理工具
2.2 創建表
Sqlite數據庫的存儲類型非常少
#-*- codeing = utf-8 -*-
#@Time : 2020/11/23 18:27
#@Author : 招財進寶
#@File : testSqlite.py
#@Software: PyCharmimport sqlite3 #在python3后默認支持的庫# conn = sqlite3.connect("test.db") #連接數據庫文件，若當前路徑下無就建立（）
# print("opened database successfully")conn = sqlite3.connect("test.db") #連接數據庫文件，若當前路徑下無就建立（）
print("成功打開數據庫")c = conn.cursor() #獲取游標，操作數據庫的對象，里面有個方法execute可以執行SQL語句#單個’是字符，兩個“”是字符串，三個'''是段落（多行字符串）
sql ='''create table company(id int primary key not null,name text not null,age int noy null,address char(50),salary real);
'''c.execute(sql) #執行SQL語句
conn.commit() #提交數據庫操作（讓sql語句真正生效）
conn.close() #關閉數據庫連接print("成功建表")
上面程序運行成功后
2.3 插入數據
可以直接在上面添加數據
#3.插入數據
conn = sqlite3.connect("test.db") #連接數據庫文件，若當前路徑下無就建立（）
print("成功打開數據庫")c = conn.cursor() #獲取游標，操作數據庫的對象，里面有個方法execute可以執行SQL語句#單個’是字符，兩個“”是字符串，三個'''是段落（多行字符串）
sql1 ='''insert into company (id, name, age, address, salary)values (1,'張三',32,"成都",8000)
'''sql2 ='''insert into company (id, name, age, address, salary)values (2,'李四',30,"重慶",15000)
'''c.execute(sql1) #執行SQL語句
c.execute(sql2) #執行SQL語句
conn.commit() #提交數據庫操作（讓sql語句真正生效）
conn.close() #關閉數據庫連接print("數據插入完畢")
2.4 查詢數據
#4.查詢數據
conn = sqlite3.connect("test.db") #連接數據庫文件，若當前路徑下無就建立（）
print("成功打開數據庫")c = conn.cursor() #獲取游標，操作數據庫的對象，里面有個方法execute可以執行SQL語句#單個’是字符，兩個“”是字符串，三個'''是段落（多行字符串）
sql ="select id,name,address,salary from company" #將里面的所有字段的數據查找出來cursor = c.execute(sql) #執行SQL語句,會有返回值，使用cursor接收for row in cursor:print("id=",row[0])print("name=", row[1])print("address=", row[2])print("salary=", row[3],"\n")conn.close() #關閉數據庫連接（因為是查詢不需要提交，直接關閉數據庫）print("數據查詢完畢")
G:\AILearning\AID2002\venv\Scripts\python.exe "G:/Python Project/shixun/test/testSqlite.py"
成功打開數據庫
id= 1
name= 張三
address= 成都
salary= 8000.0 id= 2
name= 李四
address= 重慶
salary= 15000.0 數據查詢完畢Process finished with exit code 0
2.5 保存數據到數據庫db文件
#保存數據到數據庫db文件
def savaData2DB(datalist, dbpath):#datalist是一條一條的電影信息，每個電影信息是一個列表，要將每條信息生成一個SQL語句init_db(dbpath)conn = sqlite3.connect(dbpath) # dbpath連接存在就是連接，不存在就是創建cur = conn.cursor() # 獲取游標，操作數據庫的對象，里面有個方法execute可以執行SQL語句#一條電影信息[['https://movie.douban.com/subject/1292052/', 'https://img2.doubanio.com/view/photo/s_ratio_poster/public/p480747492.jpg', '肖申克的救贖', '\xa0\xa0The Shawshank Redemption', '9.7', '2192734', '希', '導演: 弗蘭克·德拉邦特 Frank Darabont\xa0\xa0\xa0主演: 蒂姆·羅賓斯 Tim Robbins ... 1994\xa0 \xa0美國\xa0 \xa0犯罪 劇情']# sql1 ='''# insert into company (id, name, age, address, salary)# values (1,'張三',32,"成都",8000)# '''for data in datalist:for index in range(len(data)):#注意要加入雙引號進行sql語句的拼寫data[index] = '"'+data[index]+'"'sql = '''insert into movie250(info_link,pic_link,cname,ename,score,rated,instroduction,info)values(%s)'''%",".join(data) #將data每個中間用逗號連接，%是占位符，將后面的語句舔不到前面的%sprint(sql) #測試SQL語句是否正確# cur.execute(sql)# conn.commit()# cur.close()# conn.close()
（1）測試SQL語句是否拼接正確，看打印如下
insert into movie250(info_link,pic_link,cname,ename,score,rated,instroduction,info)values("https://movie.douban.com/subject/1292052/","https://img2.doubanio.com/view/photo/s_ratio_poster/public/p480747492.jpg","肖申克的救贖"," The Shawshank Redemption","9.7","2193990","希望讓人自由","導演: 弗蘭克·德拉邦特 Frank Darabont 主演: 蒂姆·羅賓斯 Tim Robbins ... 1994 美國 犯罪 劇情")insert into movie250(info_link,pic_link,cname,ename,score,rated,instroduction,info)values("https://movie.douban.com/subject/1291546/","https://img3.doubanio.com/view/photo/s_ratio_poster/public/p2561716440.jpg","霸王別姬"," ","9.6","1627621","風華絕代","導演: 陳凱歌 Kaige Chen 主演: 張國榮 Leslie Cheung 張豐毅 Fengyi Zha... 1993 中國大陸 中國香港 劇情 愛情 同性")
（2）將打印出的SQL語句直接放在database執行判斷是否成功，注意在最后的)后面不要有空格
（3）刪除db文件
（4）注意要在此處進行SQL語句的調試，調試后的語句如下，不能全是字符串，應該有的變成數值類型
insert into movie250(info_link,pic_link,cname,ename,score,rated,instroduction,info) values("https://movie.douban.com/subject/1307106/","https://img3.doubanio.com/view/photo/s_ratio_poster/public/p958008320.jpg","無間道2"," 無間道II",8.6,303304," ","導演: 劉偉強 麥兆輝 主演: 陳冠希 余文樂 曾志偉 2003 中國香港 動作 犯罪 劇情 驚悚")
（5）此時爬取成功，保存到數據庫如下
2.6 完整代碼spider.py
#-*- codeing = utf-8 -*-
#@Time : 2020/11/20 11:08
#@Author : 招財進寶
#@File : spider.py
#@Software: PyCharmfrom bs4 import BeautifulSoup #網頁解析，獲取數據
import re #正則表達式，進行文字匹配
import urllib.request,urllib.error #指定url，獲取網頁數據
import xlwt #進行Excel操作
import sqlite3 #進行SQLite操作def main():baseurl = "https://movie.douban.com/top250?start="#1.爬取網頁datalist = getData(baseurl)#3.保存數據#保存到Excel#savapath = "豆瓣電影TOP250.xls"#savaData(datalist,savapath)#3.保存到數據庫db中去dbpath = "movie.db"savaData2DB(datalist, dbpath)#askURL("https://movie.douban.com/top250?start=0")#影片詳情鏈接的規則
findLink = re.compile(r'a href="(.*?)">') #生成正則表達式對象，表示規則（字符串的模式）,用來匹配所有的鏈接
#影片 圖片的鏈接
findImgSrc = re.compile(r'<img.*src="(.*?)"',re.S) #re.S忽略里面的換行符，讓換行符包含在字符中
#影片片名
findTitle = re.compile(r'<span class="title">(.*)</span>')
#影片的評分
findRating = re.compile(r'<span class="rating_num" property="v:average">(.*)</span>')
#找到評價人數
findJudge = re.compile(r'<span>(\d*)人評價</span>')
#找到概況
findIng = re.compile(r'<span class="inq">(.*)</span>')
#找到影片的相關內容
findBd = re.compile(r'<p class="">(.*?)</p>',re.S) #re.S忽略里面的換行符，讓換行符包含在字符中#爬取網頁
def getData(baseurl):datalist=[]for i in range(0,10): #此處是爬取10頁是數據，每頁有25個電影數據（（0,10）左閉，右開）url = baseurl + str(i*25) #每一頁的url后的起始位置=頁數*25html = askURL(url) #保存獲取到的網頁源碼# 2.逐一解析數據（每個網頁解析一下）soup = BeautifulSoup(html,"html.parser") #使用html解析器html.parser解析html# find_all()查找符合要求的字符串，形成列表for item in soup.find_all('div',class_="item"): #class是個類別需要加下劃線，將及時div又是class_="item"的標簽找到#print(item) #測試：查看電影item全部信息#print(type(item))data=[] #保存一部電影的所有信息item = str(item) #str將item變成字符串#print(item)#print(type(item))#break #用來測試第一條item#接下來可以使用正則表達式對字符串進行解析了#影片詳情的鏈接link = re.findall(findLink,item)[0] #re庫用來通過正則表達式查找指定的字符串,獲取兩個相同的鏈接中的第一個#print(link)data.append(link) #添加鏈接imgSrc = re.findall(findImgSrc,item)[0]data.append(imgSrc) #添加圖片titles = re.findall(findTitle,item) #片名可能只有一個中文名，沒有外文名if(len(titles)==2) :ctitle =titles[0] #中文名的標題data.append(ctitle)otitle = titles[1].replace("/","") #去掉無關的符號data.append(otitle) #添加外國名else:data.append(titles[0]) #將第一個中文名填入data.append(' ') #留空，用于占據外國名的位置，防止位置不對稱rating = re.findall(findRating,item)[0]data.append(rating) #添加評分judgeNum = re.findall(findJudge,item)[0]data.append(judgeNum) #添加評價人數inq = re.findall(findIng,item)#（不一定每個電影都有概述）if len(inq) !=0:inq=inq[0].replace("。","") #去掉句號data.append(inq) #添加概述else:data.append(" ") #留空bd = re.findall(findBd,item)[0]bd = re.sub('<br(\s+)?/>(\s+)?'," ",bd) #去掉<br/>bd = re.sub('/'," ",bd) #替換/data.append(bd.strip()) #去掉前后的空格datalist.append(data) #把處理好的一部電影信息放入datalist#print(datalist)return datalist #返回數據列表#得到指定一個url的網頁內容
def askURL(url):#head作用是為了讓對方自己是瀏覽器，模擬瀏覽器頭部信息，向豆瓣服務器發送消息#head信息一定不要寫錯，否則會返回否碼為418，對方不認為我們是瀏覽器head = {"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36"} #如果信息不多的話，可以使用鍵值對的方式，若很多的話可以使用列表的方式head=[]#用戶代理。表示告訴豆瓣服務器。我們是什么類型的機器。瀏覽器（本質上是告訴瀏覽器。我們可以接收什么水平的文件內容）#發送消息使用下方方式request = urllib.request.Request(url=url, headers=head) # 構建的是請求對象,使用Request()封裝了請求對象,此對象包含url和head頭部信息#可以攜帶頭部信息訪問url,try:response = urllib.request.urlopen(request) #發送請求對象，返回一個response對象，此對象包含我們的網頁信息html = response.read().decode("utf-8") #將讀取的html界面讀取出來#print(html) #打印獲取的網頁except urllib.error.URLError as e: #訪問時可能會遇到404遇到一些瀏覽器內部錯誤，如500等if hasattr(e,"code"): #將里面的如404等code打印出來print(e.code)if hasattr(e, "reason"): #將產生的錯誤的原因打印出來print(e.reason)return html#保存數據到Excel
def savaData(datalist,savapath):book = xlwt.Workbook(encoding="utf-8",style_compression=0) # 創建workbook對象（可以理解為一個文件）,style_compression樣式壓縮效果sheet = book.add_sheet('豆瓣電影Top250',cell_overwrite_ok=True) # 創建工作表，可理解為文件中的表單,cell_overwrite_ok是否覆蓋掉以前的內容col = ('電影詳情鏈接',"圖片鏈接","影片中文名","影片外國名","評分","評價數","概況","相關信息")for i in range(0,8):sheet.write(0,i,col[i]) #列名for i in range(0,250):print("第%d條"%(i+1))data = datalist[i]for j in range(0,8):sheet.write(i+1,j,data[j]) #數據book.save(savapath) # 保存數據表到硬盤#保存數據到數據庫db文件
def savaData2DB(datalist, dbpath):#datalist是一條一條的電影信息，每個電影信息是一個列表，要將每條信息生成一個SQL語句init_db(dbpath)conn = sqlite3.connect(dbpath) # dbpath連接存在就是連接，不存在就是創建cur = conn.cursor() # 獲取游標，操作數據庫的對象，里面有個方法execute可以執行SQL語句#一條電影信息[['https://movie.douban.com/subject/1292052/', 'https://img2.doubanio.com/view/photo/s_ratio_poster/public/p480747492.jpg', '肖申克的救贖', '\xa0\xa0The Shawshank Redemption', '9.7', '2192734', '希', '導演: 弗蘭克·德拉邦特 Frank Darabont\xa0\xa0\xa0主演: 蒂姆·羅賓斯 Tim Robbins ... 1994\xa0 \xa0美國\xa0 \xa0犯罪 劇情']# insert into movie250(info_link,pic_link,cname,ename,score,rated,instroduction,info)# values("https://movie.douban.com/subject/1307106/",# "https://img3.doubanio.com/view/photo/s_ratio_poster/public/p958008320.jpg",# "無間道2",# " 無間道II",# 8.6,# 303304,# " ",# "導演: 劉偉強 麥兆輝 主演: 陳冠希 余文樂 曾志偉 2003 中國香港 動作 犯罪 劇情 驚悚")for data in datalist:for index in range(len(data)):#注意要加入雙引號進行sql語句的拼寫#當下標為4,5（score,rated）時，插入的類型應該是數值類型的(當遇到數值類型的不需要加雙引號，跳過保持原來的即可)if index==4 or index==5:continuedata[index] = '"'+data[index]+'"'sql = '''insert into movie250(info_link,pic_link,cname,ename,score,rated,instroduction,info)values(%s)'''%",".join(data) #將列表data每個中間用逗號連接，%是占位符，將后面的語句舔不到前面的%sprint(sql) #測試SQL語句是否正確cur.execute(sql)conn.commit()cur.close()conn.close()#創建初始化數據庫
def init_db(dbpath):sql = '''create table movie250 (id integer primary key autoincrement,info_link text,pic_link text,cname varchar,ename varchar,score numeric,rated numeric,instroduction text,info text);''' #創建數據表conn = sqlite3.connect(dbpath) #dbpath連接存在就是連接，不存在就是創建cursor = conn.cursor() #獲取游標，操作數據庫的對象，里面有個方法execute可以執行SQL語句cursor.execute(sql)conn.commit() #提交數據庫操作conn.close() #關閉數據庫連接if __name__ == "__main__": #當程序執行時#調用函數main()#init_db("movietest.db") #測試初始化是否正確print("爬取完畢！")
】)
（更新中……）)
