--
-- Database: `scrapy`
--
 
-- --------------------------------------------------------
 
--
-- 表的结构 `article`
--
 
CREATE TABLE `article` (
 `id` int(11) NOT NULL,
 `body` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
 `author` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
 `createDate` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
 
--
-- Indexes for table `article`
--
ALTER TABLE `article`
 ADD PRIMARY KEY (`id`),
 ADD UNIQUE KEY `uk_body` (`body`);

import scrapy
from tutorial.items import TutorialItem
class QuotesSpider(scrapy.Spider):
 name = "quotes"
 def start_requests(self):
  url = 'http://quotes.toscrape.com/tag/humor/'
  yield scrapy.Request(url)
 def parse(self, response):
  item = TutorialItem()
  for quote in response.css('div.quote'):
   item['body'] = quote.css('span.text::text').extract_first()
   item['author'] = quote.css('small.author::text').extract_first()
   yield item
  next_page = response.css('li.next a::attr("href")').extract_first()
  if next_page is not None:
   yield response.follow(next_page, self.parse)

# -*- coding: utf-8 -*-
# Define here the models for your scraped items
#
# See documentation in:
# https://doc.scrapy.org/en/latest/topics/items.html
import scrapy
class TutorialItem(scrapy.Item):
 body = scrapy.Field()
 author = scrapy.Field()
 pass

# -*- coding: utf-8 -*-
# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
import pymysql
import datetime
from tutorial import settings
import logging
class TutorialPipeline(object):
 def __init__(self):
  self.connect = pymysql.connect(
   host = settings.MYSQL_HOST,
   db = settings.MYSQL_DBNAME,
   user = settings.MYSQL_USER,
   passwd = settings.MYSQL_PASSWD,
   charset = 'utf8',
   use_unicode = True
  )
  self.cursor = self.connect.cursor(); 
 def process_item(self, item, spider):
  try:
   self.cursor.execute(
    "insert into article (body, author, createDate) value(%s, %s, %s) on duplicate key update author=(author)",
    (item['body'],
     item['author'],
     datetime.datetime.now()
     ))
   self.connect.commit()
  except Exception as error:
   logging.log(error)
  return item
 def close_spider(self, spider):
  self.connect.close();

ITEM_PIPELINES = {
 'tutorial.pipelines.TutorialPipeline':300
}
MYSQL_HOST = 'localhost'
MYSQL_DBNAME = 'scrapy'
MYSQL_USER = 'root'
MYSQL_PASSWD = '123456'
MYSQL_PORT = 3306

scrapy crawl quotes