供以后写代码参考:
一、同步执行
import pymysql
class CollectDataPipeline(object):
def open_spider(self, spider):
print('open' * 20)
self.conn = pymysql.connect(host="127.0.0.1", user="root", password="****", database="collect_data",
charset="utf8")
self.db_cur = self.conn.cursor()
def close_spider(self, spider):
self.conn.close()
def process_item(self, item, spider):
val = (item['title'],
item['contents'],
item['time'])
# 先删除数据
sql = '''delete from data where length(time)>0;'''
self.db_cur.execute(sql)
self.conn.commit()
# 插入新数据
sql = '''INSERT INTO data(title,contents,time) VALUES(%s,%s,%s);'''
self.db_cur.execute(sql, val)
self.conn.commit()
return item
二、异步执行
import pymysql
from twisted.enterprise import adbapi
# 异步更新操作
class LvyouPipeline(object):
def __init__(self, dbpool):
self.dbpool = dbpool
@classmethod
def from_settings(cls, settings): # 函数名固定,会被scrapy调用,直接可用settings的值
"""
数据库建立连接
:param settings: 配置参数
:return: 实例化参数
"""
adbparams = dict(
host=settings['MYSQL_HOST'],
db=settings['MYSQL_DBNAME'],
user=settings['MYSQL_USER'],
password=settings['MYSQL_PASSWORD'],
charset='utf8',
cursorclass=pymysql.cursors.DictCursor # 指定cursor类型
)
# 连接数据池ConnectionPool,使用pymysql或者Mysqldb连接
dbpool = adbapi.ConnectionPool('pymysql', **adbparams)
# 返回实例化参数
return cls(dbpool)
def process_item(self, item, spider):
"""
使用twisted将MySQL插入变成异步执行。通过连接池执行具体的sql操作,返回一个对象
"""
query = self.dbpool.runInteraction(self.do_insert, item) # 指定操作方法和操作数据
# 添加异常处理
query.addCallback(self.handle_error) # 处理异常
#其他代码都一样,SQL语句不一样即可,可复用。
def do_insert(self, cursor, item):
val=(item['Name'], item['Address'], item['Grade'], item['Score'],item['Price'])
# 对数据库进行插入操作,并不需要commit,twisted会自动commit
insert_sql = """
insert into daname(name1, address, grade, score, price) VALUES (%s,%s,%s,%s,%s)
"""
self.cursor.execute(insert_sql, val)
def handle_error(self, failure):
if failure:
# 打印错误信息
print(failure)