Scrapy 获取电影数据到excel及数据库
效果
蜘蛛程序
import scrapy
from scrapy import Selector
import sys
sys.path.append(r'D:\编程\pycharm\py文件\scrapy\spider2107\spider2107')
from items import MovieItem
class MovieSpider(scrapy.Spider):
# 爬虫名
name = 'movie'
# 爬取网站的域名
allowed_domains = ['movie.douban.com']
# 入口url
start_urls = ['https://movie.douban.com/top250']
def start_requests(self):
for page in range(10):
yield scrapy.Request(url=f'https://movie.douban.com/top250?start={page*25}&filter=')
def parse(self, response):
# 首先抓取电影列表
movie_list = response.xpath("//ol[@class='grid_view']/li")
for selector in movie_list:
# 遍历每个电影列表,从其中精准抓取所需要的信息并保存为item对象
item = MovieItem()
item['name'] = selector.xpath(".//span[@class='title']/text()").extract_first()
item['ranting'] = selector.xpath(".//span[@class='rating_num']/text()").extract_first()
item['inq'] = selector.xpath(".//span[@class='inq']/text()").extract_first()
yield item # 将结果item对象返回给Item管道
items
import scrapy
class MovieItem(scrapy.Item):
name = scrapy.Field() # 电影名
ranting = scrapy.Field() # 分数
inq = scrapy.Field() # 思想
pipelines数据管道
import openpyxl
from itemadapter import ItemAdapter
import pymysql
class Spider2107Pipeline:
def __init__(self):
self.wb = openpyxl.Workbook()
self.ws = self.wb.active
self.ws.title = 'Top250'
self.ws.append(('中心思想', '标题', '评分'))
def close_spider(self,spider):
self.wb.save('电影数据.xlsx')
def process_item(self, item, spider):
name = item.get('name', '')
rating = item.get('ranting', '')
inq = item.get('inq', '')
self.ws.append((inq, name, rating))
return item
# 连接保存到数据库
class DbPipeline:
def __init__(self):
self.conn = pymysql.connect(
host='localhost',
user='root',
password='******',
database='jiaoyang',
)
self.cursor = self.conn.cursor()
def close_spider(self, spider):
self.conn.commit()
self.conn.close()
def process_item(self, item, spider):
name = item.get('name', '')
rating = item.get('ranting', '')
inq = item.get('inq', '')
# 向数据库插入数据
self.cursor.execute(
'INSERT INTO movie(name , raking, inq) values (%s,%s,%s)',
(name, rating, inq)
)
return item
理解及错误
- 引入文件
- 管道是处理数据的。有新的数据处理模块,需要在settings中设置,数字越小代表优先值越高。
-pymysql是python连接数据库的第三方插件,拥有一些列操作。
conn = pymysql.connect( # 连接数据库
host='localhost',
user='root',
password='*******',
database='jiaoyang',
)
# 使连接对象获得一个cursor对象
cursor = conn.cursor()
# 数据库执行操作
cursor.execute(sql,param)
# 提交事物
conn.commit()
# 关闭数据库连接
conn.close()