【基础】【Python网络爬虫】【6.数据持久化】Excel、Json、Csv 数据保存（附大量案例代码）（建议收藏）

数据持久化（数据保存）

1. Excel

创建数据表

import openpyxl  # 第三方模块,  pip install openpyxl

# 1.创建一个工作簿对象
work_book = openpyxl.Workbook()

# 2.创建表对象
sheet1 = work_book.create_sheet('表1')
# 如果使用默认的表操作数据, 需要调用工作簿对象的active属性
sheet1 = work_book.active

# 3.操作表中单元格写入数据
sheet1['A1'] = 'A1'
sheet1['B7'] = 'B7'

# cell --> 单元格对象, row表示行, column表示列
sheet1.cell(row=1, column=1).value = '111111'
sheet1.cell(row=2, column=2).value = '222222'

data1 = (1, 2, 3, 4, 5)
# data2 = '45678'

# sheet1.append(序列数据)  整行添加数据到表格中去, 括号内部传递序列数据(列表/元祖)
# 通过数据的第一次和第二次数据提取, 会提取到一条一条的数据
sheet1.append(data1)
# sheet1.append(data2)

# 4.保存
work_book.save('实例.xlsx')

批量数据写入

import openpyxl

work = openpyxl.Workbook()
sheet1 = work.active

for i in range(1, 10):
    for j in range(1, i + 1):
        print(f'{j} x {i} = {j * i}', end='\t')
        sheet1.cell(row=i, column=j).value = f'{j} x {i} = {j * i}'
    print()

work.save('实例.xlsx')

读取表格数据

import openpyxl

workbook = openpyxl.load_workbook('实例.xlsx')

print(workbook.sheetnames)

sheet = workbook['Sheet']  # 指定表读取

print(sheet.max_row)  # 最大行
print(sheet.max_column)  # 最大列

# 读取第一行
for i in range(1, sheet.max_column + 1):
    print(sheet.cell(row=1, column=i).value)  # 单元格为空就返回None

# 读取第一列
for j in range(1, sheet.max_row + 1):
    print(sheet.cell(row=j, column=1).value)  # 单元格为空就返回None

for i in range(1, sheet.max_column + 1):
    for j in range(1, sheet.max_row + 1):
        print(sheet.cell(row=i, column=j).value)

案例 - 豆瓣保存 Excel

import parsel
import requests
import openpyxl

# 3.操作表中单元格写入数据
# 4.保存

# 1.创建一个工作簿对象
work = openpyxl.Workbook()
# 2.创建表对象
sheet1 = work.active
# 写表头? √

sheet1.append(['标题', '简介', '评分', '评价人数'])

for page in range(0, 226, 25):

    url = f'https://movie.douban.com/top250?start={page}&filter='
    headers = {
        'Cookie': 'll="118267"; bid=VrC8tT1GWz8; __yadk_uid=iHqVKZD4ZHIVREbOrlu9k4uWFSsAdZtO; _pk_id.100001.4cf6=b39d476add4f5658.1683638062.; __utmz=30149280.1687782730.8.7.utmcsr=baidu|utmccn=(organic)|utmcmd=organic; __utmz=223695111.1687782730.4.4.utmcsr=baidu|utmccn=(organic)|utmcmd=organic; _pk_ref.100001.4cf6=%5B%22%22%2C%22%22%2C1687952054%2C%22https%3A%2F%2Fwww.baidu.com%2Flink%3Furl%3DqdlD_RZvrHI0sXUZ08wSSKbkKLAWA_R84aALUkbWwp__yA2hUL-2C_Ej15saTpe7%26wd%3D%26eqid%3Dfdfaeaeb0001b3f60000000664998548%22%5D; _pk_ses.100001.4cf6=1; ap_v=0,6.0; __utma=30149280.1169382564.1682168622.1687782730.1687952054.9; __utmb=30149280.0.10.1687952054; __utmc=30149280; __utma=223695111.1640817040.1683638062.1687782730.1687952054.5; __utmb=223695111.0.10.1687952054; __utmc=223695111; __gads=ID=744f53c3cb2ebb52-22841ef3a4e00021:T=1683638065:RT=1687952056:S=ALNI_MZhRKuML1OBDnNRafe3qd6-ndhaiQ; __gpi=UID=00000c03bafcda5c:T=1683638065:RT=1687952056:S=ALNI_MbkLLsUm467wiS6ZZ6Mn2ohKIWBZw',
        'Host': 'movie.douban.com',
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36',
    }
    response = requests.get(url=url, headers=headers)
    html_data = response.text
    # print(html_data)

    """解析数据"""
    # 转对象
    selector = parsel.Selector(html_data)

    # 第一次提取
    lis = selector.css('.grid_view>li')

    # 二次提取
    for li in lis:
        title = li.css('.hd>a>span:nth-child(1)::text').get()
        info = li.css('.bd>p:nth-child(1)::text').getall()
        info = '//'.join([i.strip() for i in info])
        score = li.css('.rating_num::text').get()
        follow = li.css('.star>span:nth-child(4)::text').get()
        print(title, info, score, follow)

        # 调用append方法写入每一条数据
        # 写表头? x
        sheet1.append([title, info, score, follow])

    print('=' * 100 + '\n')

# 写表头? x
work.save('douban.xlsx')

# 编码
# office软件中Excel文件使用的编码是gbk
# wps软件使用的编码是 utf-8

案例 - 网易新闻Excel保存

"""
目标站点:https://news.163.com/

往下翻有 "要闻" 这个新闻类目, 找不到可以 Ctrl + F 搜索下

    需求:
        爬取网易新闻 "要闻" 类目第一页数据，将数据保存为 Excel 表格
        保存字段需要以下内容
            title
            channelname  
            docurl  
            imgurl  
            source  
            tlink
"""
import json
import re

import requests
import openpyxl

url = 'https://news.163.com/special/cm_yaowen20200213/?callback=data_callback'
response = requests.get(url=url)
json_data = response.text
# print(json_data)

result = re.findall('data_callback\((.*?)\)', json_data, re.S)
# print(result)

item_json = json.loads(result[0])
# print(item_json)
# print(type(item_json))


work = openpyxl.Workbook()
sheet1 = work.active
sheet1.append(['title', 'channelname', 'docurl', 'imgurl', 'source', 'tlink'])

for item in item_json:
    title = item['title']
    channelname = item['channelname']
    docurl = item['docurl']
    imgurl = item['imgurl']
    source = item['source']
    tlink = item['tlink']
    print(title, channelname, docurl, imgurl, source, tlink, sep=' | ')
    sheet1.append([title, channelname, docurl, imgurl, source, tlink])

work.save('网易新闻.xlsx')

2. Json

数据序列化和反序列化

import json  # 内置

# []  {}
data = {
    'name': 'ACME',
    'shares': 100,
    'price': 542.23
}

"""
json序列化: 将对象转化成json字符串
dumps()     序列化json字符串
"""
json_str = json.dumps(data)
print(json_str)
print(type(json_str))

"""
json反序列化: 将json字符串转化成对象
dumps()     序列化json字符串
"""
json_obj = json.loads(json_str)
print(json_obj)
print(type(json_obj))

中文指定

import json

data = {
    'name': '青灯',
    'shares': 100,
    'price': 542.23
}
# json字符串默认使用unicode编码, 无法显示中文
# ensure_ascii=False  不适用默认编码

json_str = json.dumps(data, ensure_ascii=False)
with open('data.json', mode='w', encoding='utf-8') as f:
    f.write(json_str)

案例 - 豆瓣保存Json

import json

import parsel
import requests
import openpyxl

data = []  # 定义一个空列表, 用于收集每一条数据

for page in range(0, 226, 25):

    url = f'https://movie.douban.com/top250?start={page}&filter='
    headers = {
        'Cookie': 'll="118267"; bid=VrC8tT1GWz8; __yadk_uid=iHqVKZD4ZHIVREbOrlu9k4uWFSsAdZtO; _pk_id.100001.4cf6=b39d476add4f5658.1683638062.; __utmz=30149280.1687782730.8.7.utmcsr=baidu|utmccn=(organic)|utmcmd=organic; __utmz=223695111.1687782730.4.4.utmcsr=baidu|utmccn=(organic)|utmcmd=organic; _pk_ref.100001.4cf6=%5B%22%22%2C%22%22%2C1687952054%2C%22https%3A%2F%2Fwww.baidu.com%2Flink%3Furl%3DqdlD_RZvrHI0sXUZ08wSSKbkKLAWA_R84aALUkbWwp__yA2hUL-2C_Ej15saTpe7%26wd%3D%26eqid%3Dfdfaeaeb0001b3f60000000664998548%22%5D; _pk_ses.100001.4cf6=1; ap_v=0,6.0; __utma=30149280.1169382564.1682168622.1687782730.1687952054.9; __utmb=30149280.0.10.1687952054; __utmc=30149280; __utma=223695111.1640817040.1683638062.1687782730.1687952054.5; __utmb=223695111.0.10.1687952054; __utmc=223695111; __gads=ID=744f53c3cb2ebb52-22841ef3a4e00021:T=1683638065:RT=1687952056:S=ALNI_MZhRKuML1OBDnNRafe3qd6-ndhaiQ; __gpi=UID=00000c03bafcda5c:T=1683638065:RT=1687952056:S=ALNI_MbkLLsUm467wiS6ZZ6Mn2ohKIWBZw',
        'Host': 'movie.douban.com',
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36',
    }
    response = requests.get(url=url, headers=headers)
    html_data = response.text
    # print(html_data)

    """解析数据"""
    # 转对象
    selector = parsel.Selector(html_data)

    # 第一次提取
    lis = selector.css('.grid_view>li')

    # 二次提取
    for li in lis:
        title = li.css('.hd>a>span:nth-child(1)::text').get()
        info = li.css('.bd>p:nth-child(1)::text').getall()
        info = '//'.join([i.strip() for i in info])
        score = li.css('.rating_num::text').get()
        follow = li.css('.star>span:nth-child(4)::text').get()
        # print(title, info, score, follow)

        d = {'title': title, 'info': info, 'score': score, 'follow': follow}
        data.append(d)

    # print('=' * 100 + '\n')

print(data)

# json数据的序列化
json_str = json.dumps(data, ensure_ascii=False)
with open('douban.json', mode='w', encoding='utf-8') as f:
    f.write(json_str)

# [{}, {}, {}......]

案例 - Json保存

"""
	目标网址：https://www.ku6.com/video/feed?pageNo=0&pageSize=40&subjectId=76
	请求方式: GET
	
	要求：
		1、请求上述网址的数据
		2、把获取到的数据保存到json文件中
            文件命名: data.json
            需要在文件中看到json字符串
			
请在下方编写代码
"""
import requests

url = 'https://www.ku6.com/video/feed?pageNo=0&pageSize=40&subjectId=76'
response = requests.get(url=url)
json_data = response.text
print(json_data)

with open('data.json', mode='w', encoding='utf-8') as f:
    f.write(json_data)

# json序列化-

3. Csv

写入csv列表数据

"""
csv数据格式:
    每一行是一条数据
    每一行中每个数据字段有分隔符号, 默认为逗号
"""
import csv  # 内置

data = [
    [1, 2, 3, 4],
    [1, 2, 3, 4],
    [5, 6, 7, 8],
    [5, 6, 7, 8]
]

with open('data.csv', mode='a', encoding='utf-8', newline='') as f:
    # newline=''  指定数据新行是一个空字符串, 不然保存会有数据空行
    # csv.writer(f)  实例化一个csv数据的写入对象, 括号内部传递文件对象
    csv_write = csv.writer(f)
    for i in data:
        # writerow(i)  把数据一行一行<一条一条>写入, 传入(列表/元组)
        csv_write.writerow(i)

案例 - 豆瓣列表保存Csv

import csv
import json

import parsel
import requests
import openpyxl

# 上下文管理器
with open('douban-list.csv', mode='a', encoding='utf-8', newline='') as f:
    csv_write = csv.writer(f)
    # csv_write.writerow(['标题', '简介', '平分', '评论人数'])
    f.write('标题,简介,平分,评论人数\n')

    for page in range(0, 226, 25):

        url = f'https://movie.douban.com/top250?start={page}&filter='
        headers = {
            'Cookie': 'll="118267"; bid=VrC8tT1GWz8; __yadk_uid=iHqVKZD4ZHIVREbOrlu9k4uWFSsAdZtO; _pk_id.100001.4cf6=b39d476add4f5658.1683638062.; __utmz=30149280.1687782730.8.7.utmcsr=baidu|utmccn=(organic)|utmcmd=organic; __utmz=223695111.1687782730.4.4.utmcsr=baidu|utmccn=(organic)|utmcmd=organic; _pk_ref.100001.4cf6=%5B%22%22%2C%22%22%2C1687952054%2C%22https%3A%2F%2Fwww.baidu.com%2Flink%3Furl%3DqdlD_RZvrHI0sXUZ08wSSKbkKLAWA_R84aALUkbWwp__yA2hUL-2C_Ej15saTpe7%26wd%3D%26eqid%3Dfdfaeaeb0001b3f60000000664998548%22%5D; _pk_ses.100001.4cf6=1; ap_v=0,6.0; __utma=30149280.1169382564.1682168622.1687782730.1687952054.9; __utmb=30149280.0.10.1687952054; __utmc=30149280; __utma=223695111.1640817040.1683638062.1687782730.1687952054.5; __utmb=223695111.0.10.1687952054; __utmc=223695111; __gads=ID=744f53c3cb2ebb52-22841ef3a4e00021:T=1683638065:RT=1687952056:S=ALNI_MZhRKuML1OBDnNRafe3qd6-ndhaiQ; __gpi=UID=00000c03bafcda5c:T=1683638065:RT=1687952056:S=ALNI_MbkLLsUm467wiS6ZZ6Mn2ohKIWBZw',
            'Host': 'movie.douban.com',
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36',
        }
        response = requests.get(url=url, headers=headers)
        html_data = response.text
        # print(html_data)

        """解析数据"""
        # 转对象
        selector = parsel.Selector(html_data)

        # 第一次提取
        lis = selector.css('.grid_view>li')

        # 二次提取
        for li in lis:
            title = li.css('.hd>a>span:nth-child(1)::text').get()
            info = li.css('.bd>p:nth-child(1)::text').getall()
            info = '//'.join([i.strip() for i in info])
            score = li.css('.rating_num::text').get()
            follow = li.css('.star>span:nth-child(4)::text').get()
            print(title, info, score, follow)

            # 循环写入数据
            csv_write.writerow([title, info, score, follow])

        print('=' * 100 + '\n')

写入csv字典数据

"""
csv数据格式:
    每一行是一条数据
    每一行中每个数据字段有分隔符号, 默认为逗号
"""

import csv  # 内置

list_dict = [{'first_name': 'Baked', 'last_name': 'Beans'},
             {'first_name': 'Lovely'},
             {'first_name': 'Wonderful', 'last_name': 'Spam'}]

with open('data.csv', mode='a', encoding='utf-8', newline='') as f:
    # 创建一个字典数据写入对象, 第一个参数是文件对象, 第二个参数是字典中的键
    # fieldnames 指定字典的键, 不能多不能少不能错
    csv_write = csv.DictWriter(f, fieldnames=['first_name', 'last_name'])
    # 字典数据会有专门写表头的方法
    csv_write.writeheader()

    for i in list_dict:
        csv_write.writerow(i)

案例 - 豆瓣字典保存csv

import csv
import json

import parsel
import requests
import openpyxl

with open('douban-dict.csv', mode='a', encoding='utf-8', newline='') as f:
    csv_write = csv.DictWriter(f, fieldnames=['title', 'info', 'score', 'follow'])
    csv_write.writeheader()  # 写表头, 只有字典数据有写表头的方法,列表没有方法写表头

    for page in range(0, 226, 25):

        url = f'https://movie.douban.com/top250?start={page}&filter='
        headers = {
            'Cookie': 'll="118267"; bid=VrC8tT1GWz8; __yadk_uid=iHqVKZD4ZHIVREbOrlu9k4uWFSsAdZtO; _pk_id.100001.4cf6=b39d476add4f5658.1683638062.; __utmz=30149280.1687782730.8.7.utmcsr=baidu|utmccn=(organic)|utmcmd=organic; __utmz=223695111.1687782730.4.4.utmcsr=baidu|utmccn=(organic)|utmcmd=organic; _pk_ref.100001.4cf6=%5B%22%22%2C%22%22%2C1687952054%2C%22https%3A%2F%2Fwww.baidu.com%2Flink%3Furl%3DqdlD_RZvrHI0sXUZ08wSSKbkKLAWA_R84aALUkbWwp__yA2hUL-2C_Ej15saTpe7%26wd%3D%26eqid%3Dfdfaeaeb0001b3f60000000664998548%22%5D; _pk_ses.100001.4cf6=1; ap_v=0,6.0; __utma=30149280.1169382564.1682168622.1687782730.1687952054.9; __utmb=30149280.0.10.1687952054; __utmc=30149280; __utma=223695111.1640817040.1683638062.1687782730.1687952054.5; __utmb=223695111.0.10.1687952054; __utmc=223695111; __gads=ID=744f53c3cb2ebb52-22841ef3a4e00021:T=1683638065:RT=1687952056:S=ALNI_MZhRKuML1OBDnNRafe3qd6-ndhaiQ; __gpi=UID=00000c03bafcda5c:T=1683638065:RT=1687952056:S=ALNI_MbkLLsUm467wiS6ZZ6Mn2ohKIWBZw',
            'Host': 'movie.douban.com',
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36',
        }
        response = requests.get(url=url, headers=headers)
        html_data = response.text
        # print(html_data)

        """解析数据"""
        # 转对象
        selector = parsel.Selector(html_data)

        # 第一次提取
        lis = selector.css('.grid_view>li')

        # 二次提取
        for li in lis:
            title = li.css('.hd>a>span:nth-child(1)::text').get()
            info = li.css('.bd>p:nth-child(1)::text').getall()
            info = '//'.join([i.strip() for i in info])
            score = li.css('.rating_num::text').get()
            follow = li.css('.star>span:nth-child(4)::text').get()
            print(title, info, score, follow)

            d = {'title': title, 'info': info, 'score': score, 'follow': follow}

            csv_write.writerow(d)

    print('=' * 100 + '\n')

读取csv数据

import csv

"""基于字符串文件类型直接读取"""
# with open('data.csv', mode='r', encoding='utf-8') as f:
#     print(f.read())


"""读取返回列表"""
# with open('douban-list.csv', mode='r', encoding='utf-8') as f:
#     csv_read = csv.reader(f)
#     print(csv_read)
#     for i in csv_read:
#         print(i)

"""读取返回字典对象的方法"""
with open('douban-list.csv', mode='r', encoding='utf-8') as f:
    csv_read = csv.DictReader(f)
    print(csv_read)
    for i in csv_read:
        print(i)

案例 - 网易新闻csv

"""
目标站点:https://news.163.com/
往下翻有 要闻 这个新闻类目

    需求:
        爬取网易新闻 要闻 类目第一页数据，将数据保存为csv格式
        保存字段需要以下内容

            title  
            channelname  
            docurl  
            imgurl  
            source  
            tlink
"""
import csv
import json
import re
import requests
import openpyxl

url = 'https://news.163.com/special/cm_yaowen20200213/?callback=data_callback'
response = requests.get(url=url)
json_data = response.text

result = re.findall('data_callback\((.*?)\)', json_data, re.S)

item_json = json.loads(result[0])

with open('网易新闻.csv', mode='a', encoding='utf-8', newline='') as f:
    write = csv.writer(f)
    write.writerow(['title', 'channelname', 'docurl', 'imgurl', 'source', 'tlink'])

    for item in item_json:
        title = item['title']
        channelname = item['channelname']
        docurl = item['docurl']
        imgurl = item['imgurl']
        source = item['source']
        tlink = item['tlink']
        print(title, channelname, docurl, imgurl, source, tlink, sep=' | ')

        write.writerow([title, channelname, docurl, imgurl, source, tlink])