五、保存数据到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: PyCharm
import 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: PyCharm
from 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后的起始位置=页数*25
html = 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: PyCharm
import 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每个中间用逗号连接，%是占位符，将后面的语句舔不到前面的%s
print(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: PyCharm
from 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后的起始位置=页数*25
html = 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:
continue
data[index] = '"'+data[index]+'"'
sql = '''
insert into movie250(info_link,pic_link,cname,ename,score,rated,instroduction,info)
values(%s)'''%",".join(data) #将列表data每个中间用逗号连接，%是占位符，将后面的语句舔不到前面的%s
print(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("爬取完毕！")
