scrapy将爬取的数据存入mysql_scrapy爬取数据存入MySQL

创建工程

scrapy startproject tutorial

创建蜘蛛

scrapy genspider crawl_mysql inputMysql http://lab.scrapyd.cn/

编写Item

class InputMysqlItem(scrapy.Item):

tags = scrapy.Field()

content = scrapy.Field()

编写创建数据库

创建数据库：

CREATE DATABASE db DEFAULT CHARACTER SET utf8

创建需要的表:CREATE TABLE crawl_my( id INTEGER NOT NULL tags VARCHAR(64) NULL, content VARCHAR(255) NULL) ENGINE = InnoDB;

编写pipline

import pymysql.cursors

class MySQLPipeline(object):

def __init__(self):

#链接数据库

self.connect = pymysql.connect(

host = '127.0.0.1',#数据库地址

port = 3306,#数据库端口

db = 'db',#数据库名称

user = 'root',#数据库用户名

passwd = 'root',#数据库密码

charset = 'utf8',#数据库编码

use_unicode = True

)

#拿到操作数据库的游标

self.cursor = self.connect.cursor()

def process_item(self,item,spider):

self.cursor.execute(

'''

insert into crawl_my(tags,content)

VALUE (%s,%s)

''',(item['tags'],item['content'])

)

#提交sql

self.connect.commit()

return item

在setting设置pipline

ITEM_PIPELINES = {

# 'tutorial.pipelines.FlowPipline': 300,

# 'tutorial.pipelines.MyImagesPipeline': 1,

'tutorial.pipelines.MySQLPipeline': 1,

# 'scrapy.contrib.pipeline.images.ImagesPipeline':1,

}

爬取spider

import scrapy

from tutorial.items import InputMysqlItem

class CrawlMysqlSpider(scrapy.Spider):

name = 'crawl_mysql'

allowed_domains = ['lab.scrapyd.cn']

start_urls = ['http://lab.scrapyd.cn/']

def parse(self, response):

item = InputMysqlItem()

for sel in response.css('div.quote'):

item['content'] = sel.css('.text::text').extract_first()

tags = sel.css('.tags .tag::text').extract()

item['tags'] = ','.join(tags)

yield item

#获取下一页链接

next_page = response.css('li.next a::attr(href)').extract_first()

if next_page is not None:

next_page = response.urljoin(next_page)

yield scrapy.Request(next_page,callback=self.parse)