def update_specific_ip(self, item):
"""更新ip"""
logger.info('Update ip, %s:%s', item['ip'], item['port'])
nowTime = get_current_time()
sql = """update db_ip.tb_ip_info set speed = %s, update_time = '%s'
where id = %s""" % (item['speed'], nowTime, item['id'])
self.db.update(sql)
def delete_expire_ip(self):
"""删除过期ip"""
logger.info('Delete expire ip....')
now_time = get_current_time(fmt = '')
update_time = (now_time - datetime.timedelta(days = self.EXPIRE_MAX_DAY))\
.strftime('%Y-%m-%d %H:%M:%S')
sql = """delete from db_ip.tb_ip_info where update_time < '%s'""" % update_time
cnt = self.db.delete(sql)
logger.info('Delete %s expire ip', cnt)
def _do_interaction(self, transaction, item, spider):
logger.info('insert ip %s:%s', item['ip'], item['port'])
nowTime = get_current_time()
sql = """insert into db_ip.tb_ip_info(uid,ip,port,anonymity,speed,update_time)
values(%s,%s,%s,%s,%s,%s)
"""
transaction.execute(sql, (item['uid'], item['ip'], item['port'],
item['anonymity'], item['speed'], nowTime))
return item
def _insert_or_update_article(self, transaction, item):
"""插入或者更新文章"""
sql = """select * from db_weixin.tb_weixin_article where uid = %s"""
transaction.execute(sql, (item['uid'], ))
ret = transaction.fetchone()
nowTime = get_current_time()
if ret is None:
# 插入文章
logging.info('insert article: %s', (item['title']))
sql = """insert into
db_weixin.tb_weixin_article(
uid,
account_id,
title,
abstract,
content,
author,
publish_time,
query,
source,
insert_time
)
values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
transaction.execute(
sql,
(
item['uid'],
item['account_id'],
item['title'],
item['abstract'],
item['content'],
item['author'],
item['publish_time'],
item['query'],
item['source'],
nowTime
)
)
else:
# 更新文章
logging.info('update article: %s', (item['title']))
sql = """update db_weixin.tb_weixin_article
set abstract = %s,content = %s, author= %s, update_time = %s
where uid = %s
"""
transaction.execute(
sql,
(
item['abstract'],
item['content'],
item['author'],
nowTime,
item['uid']
)
)
def _update_qrcode(self, item):
logging.info('update qrcode: %s %s', (item['title'],item['qrcode']))
nowTime = get_current_time()
sql = """
update db_weixin.tb_weixin_account set qrcode = '%s', update_time = '%s'
where weixin_id = %s
""" % (item['qrcode'], nowTime, item['weixin_id'])
d = self.dbpool.runQuery(sql)
d.addCallback(lambda _: item)
return d
def _insert_account(self, transaction, item):
"""插入新的微信账号"""
sql = """insert into db_weixin.tb_weixin_account(weixin_id,weixin_name,insert_time)
values(%s,%s,%s);
"""
logging.info('insert account: %s', item['weixin_name'])
nowTime = get_current_time()
transaction.execute(
sql,
(item['weixin_id'], item['weixin_name'],nowTime)
)
account_id = transaction.connection.insert_id()
return account_id