import scrapy
from ..items import ImgItem
class IvskySpider(scrapy.Spider):
  name = 'ivsky'
  allowed_domains = ['ivsky.com']
  start_urls = ['http://www.ivsky.com/tupian/ziranfengguang/']
  def parse(self, response):
    imgs = response.xpath('//div[@class="il_img"]/a/img')
    for img in imgs:
      alt = img.xpath('@alt').extract_first('')
      src = img.xpath('@src').extract_first('')
      item = ImgItem()
      item['alt'] = alt
      item['src'] = src
 
      yield item

import scrapy
 
class IvskySpiderItem(scrapy.Item):
  # define the fields for your item here like:
  # name = scrapy.Field()
  pass
 
class ImgItem(scrapy.Item):
 
  alt = scrapy.Field()
  src = scrapy.Field()

MYSQL_HOST = '127.0.0.1'
MYSQL_USER = 'root'
MYSQL_PW = '123456'
MYSQL_DB = 'ivskydb'
MYSQL_CHARSET = 'utf8'

from twisted.enterprise import adbapi
from pymysql import cursors
 
class TwistedMysqlPipeline(object):
 
  # 在調(diào)用TwistedMysqlPipeline時(shí)，第一個(gè)調(diào)用該函數(shù)
  @classmethod
  def from_settings(cls, settings):
 
    #準(zhǔn)備需要用到的鏈接mysql的參數(shù)
    db_prams = dict(
      host=settings['MYSQL_HOST'],
      user=settings['MYSQL_USER'],
      password=settings['MYSQL_PW'],
      db=settings['MYSQL_DB'],
      port=3306,
      use_unicode=True,
      charset=settings['MYSQL_CHARSET'],
      # 指定使用的游標(biāo)類型
      cursorclass=cursors.DictCursor
    )
    # 創(chuàng)建連接池對(duì)象，需要傳入兩個(gè)參數(shù)
    # 1.使用操作mysql第三方包名
    # 2.連接數(shù)據(jù)庫需要的參數(shù)
    db_pool = adbapi.ConnectionPool('pymysql', **db_prams)
 
    return cls(db_pool)
 
  def __init__(self, db_pool):
    # 將連接池對(duì)象賦值self.db_pool屬性
    self.db_pool = db_pool
 
  def process_item(self, item, spider):
 
    # 準(zhǔn)備sql
    # 執(zhí)行sql
    # 執(zhí)行一個(gè)將item數(shù)據(jù)寫入數(shù)據(jù)庫的動(dòng)作
    # 1.執(zhí)行操作的函數(shù)
    # 2.執(zhí)行函數(shù)需要的參數(shù)....
    query = self.db_pool.runInteraction(self.insert_item, item)
    # 執(zhí)行sql出現(xiàn)異常錯(cuò)誤時(shí)，回調(diào)的函數(shù)
    query.addErrback(self.handle_error, item, spider)
 
    return item
 
  # 插入數(shù)據(jù)出現(xiàn)錯(cuò)誤時(shí)，回調(diào)的函數(shù)
  def handle_error(self, failure, item, spider):
    print(failure)
    print(item)
 
  # 執(zhí)行插入數(shù)據(jù)的函數(shù)
  def insert_item(self, cursor, item):
    # 創(chuàng)建sql
    sql = "INSERT INTO ivs(alt,src)VALUES(%s,%s)"
    # 執(zhí)行sql
    cursor.execute(sql,(item['alt'], item['src']))

ITEM_PIPELINES = {
  # 'ivsky_spider.pipelines.MysqlPipeline': 300,
  'ivsky_spider.pipelines.TwistedMysqlPipeline': 300,
}