import pymysql
 
class LvyouPipeline(object):
  def __init__(self):
    # connection database
    self.connect = pymysql.connect(host='XXX', user='root', passwd='XXX', db='scrapy_test') # 后面三個(gè)依次是數(shù)據(jù)庫(kù)連接名、數(shù)據(jù)庫(kù)密碼、數(shù)據(jù)庫(kù)名稱
    # get cursor
    self.cursor = self.connect.cursor()
    print("連接數(shù)據(jù)庫(kù)成功")
 
  def process_item(self, item, spider):
    # sql語(yǔ)句
    insert_sql = """
    insert into lvyou(name1, address, grade, score, price) VALUES (%s,%s,%s,%s,%s)
    """
    # 執(zhí)行插入數(shù)據(jù)到數(shù)據(jù)庫(kù)操作
    self.cursor.execute(insert_sql, (item['Name'], item['Address'], item['Grade'], item['Score'],
                     item['Price']))
    # 提交，不進(jìn)行提交無(wú)法保存到數(shù)據(jù)庫(kù)
    self.connect.commit()
 
  def close_spider(self, spider):
    # 關(guān)閉游標(biāo)和連接
    self.cursor.close()
    self.connect.close()

import pymysql
from twisted.enterprise import adbapi
# 異步更新操作
class LvyouPipeline(object):
  def __init__(self, dbpool):
    self.dbpool = dbpool
 
  @classmethod
  def from_settings(cls, settings): # 函數(shù)名固定，會(huì)被scrapy調(diào)用，直接可用settings的值
    """
    數(shù)據(jù)庫(kù)建立連接
    :param settings: 配置參數(shù)
    :return: 實(shí)例化參數(shù)
    """
    adbparams = dict(
      host=settings['MYSQL_HOST'],
      db=settings['MYSQL_DBNAME'],
      user=settings['MYSQL_USER'],
      password=settings['MYSQL_PASSWORD'],
      cursorclass=pymysql.cursors.DictCursor  # 指定cursor類型
    )
 
    # 連接數(shù)據(jù)池ConnectionPool，使用pymysql或者M(jìn)ysqldb連接
    dbpool = adbapi.ConnectionPool('pymysql', **adbparams)
    # 返回實(shí)例化參數(shù)
    return cls(dbpool)
 
  def process_item(self, item, spider):
    """
    使用twisted將MySQL插入變成異步執(zhí)行。通過(guò)連接池執(zhí)行具體的sql操作，返回一個(gè)對(duì)象
    """
    query = self.dbpool.runInteraction(self.do_insert, item) # 指定操作方法和操作數(shù)據(jù)
    # 添加異常處理
    query.addCallback(self.handle_error) # 處理異常
 
  def do_insert(self, cursor, item):
    # 對(duì)數(shù)據(jù)庫(kù)進(jìn)行插入操作，并不需要commit，twisted會(huì)自動(dòng)commit
    insert_sql = """
    insert into lvyou(name1, address, grade, score, price) VALUES (%s,%s,%s,%s,%s)
    """
    self.cursor.execute(insert_sql, (item['Name'], item['Address'], item['Grade'], item['Score'],
                         item['Price']))
 
  def handle_error(self, failure):
    if failure:
      # 打印錯(cuò)誤信息
      print(failure)

sql = """INSERT INTO video_info(video_id, title) VALUES("%s","%s")""" % (video_info["id"],pymysql.escape_string(video_info["title"]))