Scrapy怎么将数据保存到Excel和MySQL中(excel,mysql,scrapy,开发技术)

Scrapy是一个用Python实现的为了爬取网站数据、提取数据的应用框架。我们对于爬取到的数据存储到本地或数据库是经常要用到的操作。主要讲解两种保存方式：

• Excel

• MySQL

说明一下爬取到的数据：

爬取豆瓣读书top250网页的相关信息：

书名title、链接link、国家country、作者author、翻译者translator、出版社publisher、出版时间time、价格price、星级star、评分score、评分人数people、简介comment

1. Excel

主要讲解两种方式：openpyxl和pandas

1.1 openpyxl

classExcelPipeline:def__init__(self):#创建Excel文件self.wb=Workbook()#选取第一个工作表self.ws=self.wb.active#写入表头self.ws.append(['title','link','country','author','translator','publisher','time','price','star','score','people','comment'])defprocess_item(self,item,spider):self.ws.append([item.get('title',''),item.get('link',''),item.get('country',''),item.get('author',''),item.get('translator',''),item.get('publisher',''),item.get('time',''),item.get('price',''),item.get('star',''),item.get('score',''),item.get('people',''),item.get('comment','')])returnitemdefclose_spider(self,spider):self.wb.save('result.xlsx')

1.1.1 代码说明

ExcelPipeline 继承自 Scrapy 的 Pipeline 类，并重写了三个方法：__init__()、process_item() 和 close_spider()。

在 __init__() 方法中：

• 创建了一个 Excel 文件，并选取了第一个工作表。然后，我们写入了表头。

• 当然你也可以将这部分代码写在open_spider方法中

在 process_item() 方法中，我们将每一行的数据写入到工作表中。

process_item 方法：

• 不会覆盖之前已经写入的数据，它会在数据末尾追加新的行。

• 你调用多次 process_item 方法，每次都会在表格的末尾追加一行新数据。

在 close_spider() 方法中，我们保存 Excel 文件。

1.1.2 注意

可以发现我在process_item()方法中使用了item.get(key, default)：

考虑可能存在某些 item 中没有某些键值的情况，这可能会导致程序出错。

当然如果你已经进行过数据处理也可以直接用item[key]。

使用了 item.get(key, default) 方法来获取 item 中的键值，如果某个键不存在，则返回一个空字符串 ''

在 Scrapy 中，item 是一个字典类型，它由一系列键值对组成，每个键值对表示一个字段。在处理 item 时，我们通常需要从中获取某个字段的值。使用字典的 get 方法可以方便地实现这个功能。

get 方法有两个参数：key 表示要获取的键，default 表示键不存在时的默认值。例如：

1.2 pandas

classExcelPipeline:def__init__(self):#创建一个空的数据框self.df=pd.DataFrame(columns=['title','link','country','author','translator','publisher','time','price','star','score','people','comment'])defprocess_item(self,item,spider):#将数据添加到数据框中item['title']=item.get('title','')item['link']=item.get('link','')item['country']=item.get('country','')item['author']=item.get('author','')item['translator']=item.get('translator','')item['publisher']=item.get('publisher','')item['time']=item.get('time','')item['price']=item.get('price','')item['star']=item.get('star','')item['score']=item.get('score','')item['people']=item.get('people','')item['comment']=item.get('comment','')series=pd.Series(item)self.df=self.df.append(series,ignore_index=True)returnitemdefclose_spider(self,spider):#将数据框保存到Excel文件中self.df.to_excel('result.xlsx',index=False)

1.2.1 代码说明

定义了一个 ExcelPipeline 类，它包含了三个方法：__init__、process_item 和 close_spider。

• __init__ 方法用于初始化类实例

• process_item 方法用于处理每个爬取到的 item，将其添加到 items 列表中

• close_spider 方法用于在爬虫关闭时将 items 列表中的数据保存到 Excel 文件中。

1.2.2 常见错误

在代码中有大量的item['title'] = item.get('title', '')类似代码

你可以选择不写，但如果item中有一些字段的值为None，而pandas不支持将None类型的值添加到DataFrame中，会导致程序错误。这一点比openpyxl要严格的多。

字典对象转换为Series对象

self.df是一个DataFrame对象，而item是一个字典对象。因此，需要将字典对象转换为Series对象，然后再将其添加到DataFrame中。

series=pd.Series(item)self.df=self.df.append(series,ignore_index=True)

only Series and DataFrame objs are valid这个错误一般就是发生在使用Pandas将数据转换成DataFrame时，传入的参数不是Series或DataFrame类型。

上面的代码就是用来避免这个问题的。

1.3 openpyxl和pandas对比

pandas和openpyxl都是非常强大的Python数据处理库，两者在不同的场景下可以发挥出各自的优势。

• 如果需要处理大量的Excel文件，需要对文件进行复杂的操作，比如格式化、图表等，那么openpyxl可能更适合，因为它专注于Excel文件的读写和操作，具有更高的灵活性和控制力。

• 如果数据已经在Python中，且需要进行各种统计分析和处理，如数据聚合、数据透视表、数据分组、数据清洗、数据可视化等，那么pandas可能更适合，因为它提供了丰富的数据处理工具和函数。

总的来说，两者都是很好的工具，具体使用哪一个取决于具体需求和场景。

2. MYSQL

可以使用Python的MySQL驱动程序，例如 mysql-connector-python 或 pymysql。主要将pymysql。

classMySQLPipeline:def__init__(self):#连接MySQL数据库self.conn=pymysql.connect(host='localhost',port=3306,user='root',password='your_password',database='your_database',charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)#创建一个游标对象self.cursor=self.conn.cursor()#创建表self.create_table()defcreate_table(self):#SQL语句：创建数据表sql='''CREATETABLEIFNOTEXISTS`book`(`id`int(11)NOTNULLAUTO_INCREMENT,`title`varchar(255)NOTNULL,`link`varchar(255)NOTNULL,`country`varchar(255)NOTNULL,`author`varchar(255)NOTNULL,`translator`varchar(255)NOTNULL,`publisher`varchar(255)NOTNULL,`time`varchar(255)NOTNULL,`price`varchar(255)NOTNULL,`star`varchar(255)NOTNULL,`score`varchar(255)NOTNULL,`people`varchar(255)NOTNULL,`comment`varchar(255)NOTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ci'''#执行SQL语句self.cursor.execute(sql)#提交事务self.conn.commit()defprocess_item(self,item,spider):#SQL语句：插入数据sql='''INSERTINTO`book`(`title`,`link`,`country`,`author`,`translator`,`publisher`,`time`,`price`,`star`,`score`,`people`,`comment`)VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''#执行SQL语句self.cursor.execute(sql,(item['title'],item['link'],item['country'],item['author'],item['translator'],item['publisher'],item['time'],item['price'],item['star'],item['score'],item['people'],item['comment']))#提交事务self.conn.commit()returnitemdefclose_spider(self,spider):#关闭游标对象self.cursor.close()#关闭数据库连接self.conn.close()

2.1 代码说明

我们创建了一个名为MySQLPipeline的自定义ScrapyPipeline。

__init__方法中接收了MySQL数据库的配置信息。

其中还调用了create_table，当然如果保证表已经存在，也没有必要这么写

如果你嫌每次连接都要写信息的话，可以在setting.py中定义MySQL相关变量：

create_table方法创建表book

process_item方法用于将抓取的数据插入到数据库表中。

close_spider方法用于关闭游标和连接。

2.2 pymysql介绍

2.2.1 游标对象

在Python中，连接数据库时需要创建一个数据库连接对象，然后通过这个连接对象创建一个游标对象。

游标对象是执行数据库操作的主要对象，它负责向数据库发送查询和获取结果。

在Python中，常用的游标对象有Cursor、DictCursor、SSCursor等。

• Cursor：普通游标（默认），返回结果为元组类型。

• DictCursor：字典游标，返回结果为字典类型。

• SSCursor：嵌套游标，可用于处理大数据集。

在获取大量数据时效率比普通游标更高，但是会占用更多的系统资源。

与普通游标相比，嵌套游标不会将整个查询结果读入内存，而是每次只读取部分数据。

根据需要，选择不同类型的游标对象可以方便我们对返回结果进行处理。

2.2.2 各种游标说明

创建连接对象时有这么一段代码：

cursorclass=pymysql.cursors.DictCursor

用于设置游标返回的数据类型，默认返回的是元组(tuple)类型，设置为DictCursor后可以返回字典(dict)类型，更方便处理数据。一般使用普通游标就行了

三种游标主要是在查询时的方式存在区别：

cur=conn.cursor()cur.execute('SELECT*FROMmy_table')result=cur.fetchone()#获取一条记录，返回的是元组类型#普通游标print(result[0])#访问第一个字段的值#字典游标print(result['id'])#访问数据库中字段名为id的字段的值，{'id':1,'name':'Alice'}#嵌套游标print(result[0])#访问第一个字段的值

如果是查询的多条数据，则返回的是元组或字典组成的列表：

#普通游标[(1,'John','Doe'),(2,'Jane','Doe'),(3,'Bob','Smith')]#字典游标[{'id':1,'first_name':'John','last_name':'Doe'},{'id':2,'first_name':'Jane','last_name':'Doe'},{'id':3,'first_name':'Bob','last_name':'Smith'}]

3. 特别说明

每个item在被提交给管道时都会调用一次管道类的process_item方法。

每个item都会经过process_item方法进行处理，而open_spider和close_spider方法只会在爬虫启动和结束时执行一次。

在Scrapy中，可以通过在管道类的open_spider和close_spider方法中建立和关闭数据库连接，以减少连接建立和关闭的次数。

__init__方法也是只在Spider启动时只执行一次

具体做法是，在open_spider方法中建立数据库连接，在process_item方法中使用连接对数据进行存储操作，在close_spider方法中关闭连接。这样做可以有效减少连接的建立和关闭次数，提高爬取效率。

如果你在open_spider方法中创建了数据库连接，那么这个连接将会被共享并被多个process_item方法使用。

同样的，如果在close_spider方法中关闭了数据库连接，那么这个连接也会被所有的process_item方法共享并在爬虫结束时关闭。

这种做法可以减少不必要的连接和关闭操作，从而提高性能。