scrapy+mysql存储爬虫数据

一、创建数据库和数据表

• 命令行

#创建‘heixiu’数据库，并指定字符集
CREATE DATABASE `heixiu` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
#切换到数据库
use heixiu;
#创建数据表
CREATE TABLE heixiumovie (id int primary key auto_increment ,title VARCHAR(100) NOT NULL,video_url VARCHAR(100) NOT NULL);

二、配置mysql账号信息

• setting.py

MYSQL_HOST = 'localhost'
MYSQL_DBNAME = 'douban'
MYSQL_USER = 'root'
MYSQL_PASSWD = '123456'

三、创建管道

• pipelines.py

# 用于数据库存储
class DBPipeline(object):
    def __init__(self):
        settings = get_project_settings()
        self.host = settings['MYSQL_HOST']
        self.user = settings['MYSQL_USER']
        self.pwd = settings['MYSQL_PASSWD']
        self.name = settings['MYSQL_DBNAME']
        self.charset = settings['MYSQL_CHARSET']

        self.connect()

    def connect(self):
        self.conn = pymysql.connect(host=self.host,
                             port=3306,
                             user=self.user,
                             password=self.pwd,
                             db=self.name,
                             charset=self.charset)
        self.cursor = self.conn.cursor()

    def close_spider(self, spider):
        self.conn.close()
        self.cursor.close()

    def process_item(self, item, spider):
        try:
            # 查重处理
            self.cursor.execute(item['url'])
            # 是否有重复数据
            repetition = self.cursor.fetchone()
            # 重复
            if repetition:
                pass
            else:
                # 插入数据
                self.cursor.execute(
                    (item['title'],
                     item['url'],))

        except Exception as error:
            # 出现错误时打印错误信息
            print(error)
        return item

四、注册管道

• settings.py

ITEM_PIPELINES = {
   'heihei.pipelines.HeiheiPipeline': 300,
   +'heihei.pipelines.DBPipeline':50,
}