python使用adbapi实现MySQL数据库的异步存储

# -*- coding: utf-8 -*-
import io
import sys
import scrapy
import re
import datetime
from scrapy.http import Request
from urllib import parse
from ArticleSpider.items import JobboleArticleItem, ArticleItemLoader
from scrapy.loader import ItemLoader
sys.stdout = io.TextIOWrapper(sys.stdout.buffer,encoding='utf-8')
 
class JobboleSpider(scrapy.Spider):
 """docstring for JobboleSpider"""
 name = "jobbole"
 allowed_domain = ["blog.jobbole.com"]
 start_urls = ['http://blog.jobbole.com/all-posts/']
 
 def parse(self, response):
 """
 1.获取列表页中的文章url
 """
 # 解析列表汇中所有文章url并交给scrapy下载器并进行解析
 post_nodes = response.css("#archive .floated-thumb .post-thumb a")
 for post_node in post_nodes:
 image_url = post_node.css("img::attr(src)").extract_first("")# 这里取出每篇文章的封面图，并作为meta传入Request
 post_url = post_node.css("::attr(href)").extract_first("")
 yield Request(url = parse.urljoin(response.url, post_url), meta = {"front_image_url":image_url}, callback = self.parse_detail)
 
 def parse_detail(self, response):
 article_item = JobboleArticleItem()
 # 通过ItemLoader加载Item
 # 通过add_css后的返回值都是list型，所有我们再items.py要进行处理
 item_loader = ArticleItemLoader(item = JobboleArticleItem(), response = response)
 item_loader.add_css("title", ".entry-header h1::text")
 item_loader.add_value("url", response.url)
 # item_loader.add_value("url_object_id", get_md5(response.url))
 item_loader.add_value("url_object_id", response.url)
 item_loader.add_css("create_date", "p.entry-meta-hide-on-mobile::text")
 item_loader.add_value("front_image_url", [front_image_url])
 item_loader.add_css("praise_nums", ".vote-post-up h10::text")
 item_loader.add_css("comment_nums", "a[href='#article-comment'] span::text")
 item_loader.add_css("fav_nums", ".bookmark-btn::text")
 item_loader.add_css("tags", "p.entry-meta-hide-on-mobile a::text")
 item_loader.add_css("content", "div.entry")
 
 article_item = item_loader.load_item()
 print(article_item["tags"])
 
 yield article_item
 pass

# -*- coding: utf-8 -*-
 
# Define here the models for your scraped items
#
# See documentation in:
# https://doc.scrapy.org/en/latest/topics/items.html
import datetime
import re
 
 
import scrapy
from scrapy.loader import ItemLoader
from scrapy.loader.processors import MapCompose, TakeFirst,Join
from ArticleSpider.utils.common import get_md5
 
 
def convert_date(value):
 try:
 create_date = datetime.datetime.strptime(create_date, "%Y/%m/%d").date()
 except Exception as e:
 create_date = datetime.datetime.now().date()
 return create_date
 
def get_nums(value):
 match_re = re.match(".*?(\d+).*", value)
 if match_re:
 nums = int(match_re.group(1))
 else:
 nums = 0
 
 return nums
 
def remove_comment_tags(value):
 # 去掉tags中的评论内容
 if "评论" in value:
 # 这里做了修改，如果返回""，则在list中仍然会占位，会变成类似于["程序员",,"解锁"]这样
 # return ""
 return None 
 else:
 return value
 
def return_value(value):
 return 
 
class ArticleItemLoader(ItemLoader):
 """docstring for AriticleItemLoader"""
 # 自定义ItemLoader
 default_output_processor = TakeFirst()
 
class ArticlespiderItem(scrapy.Item):
 # define the fields for your item here like:
 # name = scrapy.Field()
 pass
 
class JobboleArticleItem(scrapy.Item):
 """docstring for ArticlespiderItem"""
 title = scrapy.Field()
 create_date = scrapy.Field(
 input_processor = MapCompose(convert_date)
 )
 url = scrapy.Field()
 url_object_id = scrapy.Field(
 output_processor = MapCompose(get_md5)
 )
 # 这里注意front_image_url还是一个list，在进行sql语句时还需要处理
 front_image_url = scrapy.Field(
 output_processor = MapCompose(return_value)
 )
 front_image_path = scrapy.Field()
 praise_nums = scrapy.Field(
 input_processor = MapCompose(get_nums)
 )
 comment_nums = scrapy.Field(
 input_processor = MapCompose(get_nums)
 )
 fav_nums = scrapy.Field(
 input_processor = MapCompose(get_nums)
 )
 # tags要做另行处理，因为tags我们需要的就是list
 tags = scrapy.Field(
 input_processor = MapCompose(remove_comment_tags),
 output_processor = Join(",")
 )
 content = scrapy.Field()

# -*- 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 codecs
import json
from twisted.enterprise import adbapi
import MySQLdb
import MySQLdb.cursors
 
 
class MysqlTwistedPipeline(object):
 """docstring for MysqlTwistedPipeline"""
 #采用异步的机制写入mysql
 def __init__(self, dbpool):
 self.dbpool = dbpool
 
 @classmethod
 def from_settings(cls, settings):
 dbparms = dict(
 host = settings["MYSQL_HOST"],
 db = settings["MYSQL_DBNAME"],
 user = settings["MYSQL_USER"],
 passwd = settings["MYSQL_PASSWORD"],
 charset='utf8',
 cursorclass=MySQLdb.cursors.DictCursor,
 use_unicode=True,
 )
 dbpool = adbapi.ConnectionPool("MySQLdb", **dbparms)
 
 return cls(dbpool)
 
 def process_item(self, item, spider):
 #使用twisted将mysql插入变成异步执行
 query = self.dbpool.runInteraction(self.do_insert, item)
 query.addErrback(self.handle_error, item, spider) #处理异常
 return item
 
 def handle_error(self, failure, item, spider):
 # 处理异步插入的异常
 print (failure)
 
 def do_insert(self, cursor, item):
 #执行具体的插入
 #根据不同的item 构建不同的sql语句并插入到mysql中
 # insert_sql, params = item.get_insert_sql()
 # print (insert_sql, params)
 # cursor.execute(insert_sql, params)
 insert_sql = """
 insert into jobbole_article(title, url, create_date, fav_nums, url_object_id)
 VALUES (%s, %s, %s, %s, %s)
 """
 # 可以只使用execute，而不需要再使用commit函数
 cursor.execute(insert_sql, (item["title"], item["url"], item["create_date"], item["fav_nums"], item["url_object_id"]))

MYSQL_HOST = "localhost"
MYSQL_DBNAME = "article_wilson"
MYSQL_USER = "root"
MYSQL_PASSWORD = "root"

ITEM_PIPELINES = {
 # 'ArticleSpider.pipelines.ArticlespiderPipeline': 300,
 # 'ArticleSpider.pipelines.JsonWithEncodingPipeline': 1
 
 # # 'scrapy.pipelines.images.ImagePipeline': 1,
 # 'ArticleSpider.pipelines.JsonExporterPipleline': 1
 # 'ArticleSpider.pipelines.ArticleImagePipeline': 2
 # 'ArticleSpider.pipelines.MysqlPipeline': 1
 'ArticleSpider.pipelines.MysqlTwistedPipeline': 1
}