#-*- coding: utf-8 -*-
importsys
sys.path.append("/apps/jr_python/riskspiders")from riskspiders.utils importDButilfrom riskspiders.settings importDATABASE_PRMimportloggingimporthashlib
logger= logging.getLogger(__name__)#Define your item pipelines here#
#Don‘t forget to add your pipeline to the ITEM_PIPELINES setting#See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
classRiskspidersPipeline(object):#连接数据库,只要类一初始化,就已经连好了数据库
db =DButil(DATABASE_PRM)defprocess_item(self, item, spider):returnitemclassRiskspidersMySQLPipeline(object):#连接数据库,只要类一初始化,就已经连好了数据库
#def __init__(self):
#self.md = hashlib.md5()
defopen_spider(self, spider):print("open_spider, %s" %spider.name)
self.db=DButil(DATABASE_PRM)for day inspider.day_list:
sql_del= """delete from riskinfo where spider = ‘{}‘ and release_time = ‘{}‘;""".format(spider.name,day)try:
self.db.execute(sql_del)exceptException as e:print(e)defclose_spider(self,spider):
self.db.close()#以下可以打印大部分的数据收集,但是finish_time等不能输出,因为程序还没有运行完
print(spider.crawler.stats.get_value())defprocess_item(self,item,spider):
db=DButil(DATABASE_PRM)#逐条插入,更新插入
if spider.name == ‘hexun_bankdata‘:#print(‘***** item_bank insert MySQL‘)
logger.info(‘***** item_bank insert MySQL‘)
pa=(
item["source"], item["spider"],item[‘website_menu‘], item["disclosure_period"], item["bank_abbreviation"], item["total_assets"],
item["capital_adequancy_ratio"], item["core_capital_adequancy_ratio"], item["bad_loan_ratio"],
item["provision_coverage"], item["url"], item["cra_time"], item["cra_time"])
sql_data="""insert into hexun_bankdata(source,spider,website_menu,disclosure_period, bank_abbreviation,total_assets,capital_adequancy_ratio,core_capital_adequancy_ratio,bad_loan_ratio,provision_coverage,url,cra_time) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) on duplicate key update cra_time = %s;"""
try:
db.execute(sql_data, pa)exceptException as e:printe
logger.error(e)finally:
db.close()else:
md=hashlib.md5()
str1= ‘%s%s‘ % (item[‘title‘], item[‘content‘])
md.update(str1)
md_value=md.hexdigest()#print("str1 is %s,md_value is %s" % (str1,md_value))
logger.info(‘***** item_bank insert MySQL‘)
params=(
item[‘source‘], item[‘spider‘],item[‘website_menu‘], item[‘release_time‘], item[‘key_words‘], item[‘neg_key_words‘], item[‘title‘].strip(),
item[‘source_type‘], item[‘f_name‘], item[‘is_include_tbl‘], item[‘content‘].strip(), item[‘content_web‘], item[‘url‘],
item[‘father_url‘], item[‘cra_time‘], md_value, item[‘cra_time‘]
)try:
db.execute("""insert into riskinfo
(source, spider,website_menu, release_time, key_words,neg_key_words, title, source_type,f_name, is_include_tbl,content,content_web, url,father_url,cra_time,content_id)
values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) on duplicate key update cra_time = %s;""", params
)exceptException as e:printe
logger.error(e)finally:
db.close()