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()