def push_queue_items(): count_news_seed_queue_sql = """select count(*) from hainiu_queue where type=3 and fail_times=0;""" insert_news_seed_internally_queue_items_sql = """ insert into hainiu_queue (type,action,params) values(3,%s,%s); """ count_news_seed_internally_sql = """select count(*) from hainiu_web_seed_internally where status=0 for update;""" selec_news_seed_internally_sql = """select a_url,param,id from hainiu_web_seed_internally where status=0 limit %s,%s;""" update_news_seed_internally_sql = """update hainiu_web_seed_internally set status=1 where id in (%s);""" rl = LogUtil().get_base_logger() try: d = DBUtil(config._HAINIU_DB) queue_total = d.read_one(count_news_seed_queue_sql)[0] if queue_total != 0: rl.info('last download_page queue not finish,last queue %s unFinish' % (queue_total)) return starttime = time.clock() d = DBUtil(config._HAINIU_DB) total = long(d.read_one(count_news_seed_internally_sql)[0]) page_size = 1000 page = total / page_size for i in range(0, page + 1): sql = selec_news_seed_internally_sql % (0, page_size) list = d.read_tuple(sql) values = [] id_values = [] for l in list: url = l[0] url = url if url is not None else '' param = l[1] param = param if param is not None else '' values.append((url,param)) id = l[2] id_values.append(str(id)) if id_values.__len__() != 0: random.shuffle(values) d.executemany_no_commit(insert_news_seed_internally_queue_items_sql,values) ids = ','.join(id_values) sql = update_news_seed_internally_sql % (ids) d.execute(sql) endtime = time.clock() worksec = int(round((endtime - starttime))) rl.info('push seed_internally queue finish,total items %s,action time %s\'s' % (total,worksec)) except: rl.exception() rl.error(sql) d.rollback() finally: d.close()
def fail_action(self, values): ''' 消息动作处理失败之后,更改队列中间件中该消息的失败次数并记录执行机器的IP 如果达到该机器的最大尝试失败次数,则更改队列中间件中该消息的状态为未处理,目的让其它机器再次尝试去处理该消息 :param values: 消息动作处理之后的结果 ''' update_sql = """ update hainiu_queue set fail_times=fail_times+1,fail_ip='%s' where id=%s; """ update_sql_1 = """ update hainiu_queue set type=1 where id=%s """ try: d = DBUtil(config._HAINIU_DB) id = values[0] u = Util() ip = u.get_local_ip() sql = update_sql % (ip, id) d.execute_no_commit(sql) if (self.try_num == Consumer._WORK_TRY_NUM): sql = update_sql_1 % id d.execute_no_commit(sql) d.commit() except: self.rl.exception() self.rl.error(sql) d.rollback() finally: d.close()
def queue_items(self): select_queue_sql = """ select id,action,params from hainiu_queue where type=1 and fail_times <= %s limit 0,%s for UPDATE; """ update_queue_sql = """ update hainiu_queue set type=0 where id in (%s); """ list = [] try: d = DBUtil(config._HAINIU_DB) sql = select_queue_sql % (self.fail_times,self.limit) tuple = d.read_tuple(sql) if len(tuple) == 0: return list queue_ids = '' for t in tuple: queue_id = t[0] url = t[1] param = '' if t[2] is None else t[2] queue_ids += str(queue_id) + ',' c = NewsFindConsumer(url, param, queue_id) list.append(c) queue_ids = queue_ids[:-1] d.execute(update_queue_sql % (queue_ids)) except: self.rl.exception() d.rollback() d.commit() finally: d.close() return list
def fail_action(self, values): update_sql = """ update hainiu_queue set fail_times=fail_times+1,fail_ip='%s' where id=%s; """ update_sql_1 = """ update hainiu_queue set type=1 where id=%s; """ update_hainiu_news_seed_sql = """ update hainiu_web_seed set fail_times=fail_times+1,fail_ip="%s" where md5="%s"; """ try: d = DBUtil(config._HAINIU_DB) id = values[5] u = Util() ip = u.get_local_ip() sql = update_sql % (ip, id) d.execute_no_commit(sql) main_md5 = values[0] sql = update_hainiu_news_seed_sql % (ip, main_md5) d.execute_no_commit(sql) if (self.try_num == Consumer._WORK_TRY_NUM): sql = update_sql_1 % (id) d.execute_no_commit(sql) d.commit() except: self.rl.exception() self.rl.error(sql) d.rollback() d.commit() finally: d.close()
def fail_action(self, values): ip = Util().get_local_ip() db_util = DBUtil(_HAINIU_DB) #1)记录队列表错误次数和ip; queue_update_sql1 = """ update hainiu_queue set fail_times=fail_times+1,fail_ip=%s where id=%s; """ #2)当某个机器的错误次数达到了当前机器设定的最大重试次数,把hainiu_queue 表对应的记录的 #is_work = 0,让其他机器重试; queue_update_sql2 = """ update hainiu_queue set is_work=0 where id=%s; """ #3)更新内链表的失败次数和失败ip,队列表的数据不删除; inner_update_sql = """ update hainiu_web_seed_internally set fail_times=fail_times+1,fail_ip=%s where md5=%s and a_md5=%s """ try: # 1) sql_params = [ip, values[0]] db_util.execute_no_commit(queue_update_sql1, sql_params) # 2) # 比较失败次数 if self.current_retry_num == _QUEUE_NEWS_FIND['C_RETRY_TIMES'] - 1: sql_params = [self.id] db_util.execute_no_commit(queue_update_sql2, sql_params) sql_params = [ip, values[1], values[2]] db_util.execute_no_commit(inner_update_sql, sql_params) db_util.commit() except Exception, e: db_util.rollback() traceback.print_exc(e)
def fail_action(self, values): ip = Util().get_local_ip() db_util = DBUtil(_HAINIU_DB) #1)记录hainiu_queue表错误次数和ip; # is_success,self.id,len(inner_list),len(exter_list),md5 queue_update_sql1 = """ update hainiu_queue set fail_times=fail_times+1,fail_ip=%s where id=%s; """ #2)当某个机器的错误次数达到了当前机器设定的最大重试次数,把hainiu_queue # 表对应的记录的 is_work = 0,让其他机器重试; queue_update_sql2 = """ update hainiu_queue set is_work=0 where id=%s; """ #3)更新种子表的失败次数、失败ip;队列表的数据不删除,有可能是因为目标网站把ip给封了, # 在某个时间,写个脚本,把失败的队列数据改状态和失败次数和失败ip,重新爬取试试。 seed_update_sql = """ update hainiu_web_seed set fail_times=fail_times+1,fail_ip=%s where md5=%s """ try: sql_params = [ip, values[0]] db_util.execute_no_commit(queue_update_sql1, sql_params) # 比较失败次数 if self.current_retry_num == _QUEUE_NEWS_FIND['C_RETRY_TIMES'] - 1: sql_params = [self.id] db_util.execute_no_commit(queue_update_sql2, sql_params) sql_params = [ip, values[3]] db_util.execute_no_commit(seed_update_sql, sql_params) db_util.commit() except Exception, e: traceback.print_exc(e) db_util.rollback()
def put_inner_to_queue(): redis_util = RedisUtill() ''' ''' page_show_num = 10 # 统计hainiu_queue 未处理的记录数 select_queue_count_sql = """ select count(*) from hainiu_queue where type=%s and is_work=0 and fail_times=0; """ # 插入hainiu_queue表 insert_queue_sql = """ insert into hainiu_queue (type,action,params) values (%s, %s, %s); """ logger = LogUtil().get_logger("download_news_queue", "download_news_queue") db_util = DBUtil(_HAINIU_DB) db_util.execute_no_commit("set NAMES utf8mb4;") try: # 统计hainiu_queue 未处理的记录数 sql_params = [2] res1 = db_util.read_one(select_queue_count_sql, sql_params) queue_count = res1[0] start_time = time.time() if queue_count >= 5: logger.info("hainiu_queue 有 %d 条未处理的记录,不需要导入!" % queue_count) return None inner_count = 0 for ip in ips: key_list = [] scan_limit_to_queue_table(ip, port, 0, 'down:*', 20, key_list) inner_count = inner_count + len(key_list) # 根据key列表上Redis里获取value列表 values = redis_util.get_values_batch_keys(key_list) # 导入hainiu_queue表 insert_queue_record = [] for value in values: queue_param = json.loads(value) a_url = queue_param['a_url'] insert_queue_record.append((2, a_url, value)) db_util.executemany_no_commit(insert_queue_sql, insert_queue_record) db_util.commit() # 把导入表后的key列表从redis里删掉 redis_util.delete_batch(key_list) end_time = time.time() run_time = end_time - start_time logger.info("本地导入 %d 条数据, 用时 %.2f 秒" % (inner_count, run_time)) except Exception, e: traceback.print_exc(e) db_util.rollback()
def queue_items(self): ''' 从队列中取出要处理的消息,并封装成消费者动作,然后更新队列的状态 :return: 封装好的消费者动作列表 ''' # 会限制本机处理失败之后就不再进行获取的获取,通过机器IP来限制 # select_queue_sql = """ # select id,action,params from hainiu_queue where type=1 and fail_ip <>'%s' and fail_times<=%s # limit 0,%s for update; # """ select_queue_sql = """ select id,action,params from hainiu_queue where type=1 and fail_times<=%s limit 0,%s for update; """ update_queue_sql = """ update hainiu_queue set type=0 where id in (%s); """ return_list = [] try: d = DBUtil(config._HAINIU_DB) # u = Util() # ip = u.get_local_ip() # sql = select_queue_sql % (self.fail_times,ip,self.limit) sql = select_queue_sql % (self.fail_times, self.limit) select_dict = d.read_dict(sql) if len(select_dict) == 0: return return_list query_ids = [] for record in select_dict: id = record["id"] action = record["action"] params = record["params"] query_ids.append(str(id)) c = HainiuConsumer(id, action, params) return_list.append(c) ids = ",".join(query_ids) sql = update_queue_sql % ids d.execute(sql) except: self.rl.exception() self.rl.error(sql) d.rollback() finally: d.close() return return_list
def push_queue_items(): count_news_seed_sql = """select count(*) from hainiu_web_seed where status=0;""" select_news_seed_sql = """select url,category,last_crawl_time from hainiu_web_seed where status=0 limit %s,%s;""" insert_news_seed_queue_items_sql = """insert into hainiu_queue (type,action,params) values(1,%s,%s);""" count_news_seed_queue_sql = """select count(*) from hainiu_queue where type=1 and fail_times=0;""" rl = LogUtil().get_base_logger() try: d = DBUtil(config._HAINIU_DB) queue_total = d.read_one(count_news_seed_queue_sql)[0] if queue_total != 0: rl.info('last news_find queue not finish,last queue %s unFinish' % (queue_total)) return starttime = time.clock() total = long(d.read_one(count_news_seed_sql)[0]) page_size = 1000 page = total / page_size for i in range(0, page + 1): sql = select_news_seed_sql % (i * page_size, page_size) list = d.read_tuple(sql) values = [] for l in list: url = l[0] publisher = get_tld(url) publisher = publisher[0:publisher.index(( '.'))] if publisher.__contains__('.') else publisher param = {} param['category'] = l[1] param['publisher'] = publisher param = json.dumps(param, ensure_ascii=False) values.append((url, param)) if values.__len__() != 0: random.shuffle(values) d.executemany(insert_news_seed_queue_items_sql, values) endtime = time.clock() worksec = int(round((endtime - starttime))) rl.info( 'push news_find queue finish,total items %s,action time %s\'s' % (total, worksec)) except: rl.exception() rl.error(sql) d.rollback() finally: d.close()
def queue_items(self): ''' 通过悲观锁+事务+更新状态来实现多个机器串行拿取数据, 并把其封装成HainiuConsumerAction对象实例列表返回 ''' select_sql = """ select id,action,params from hainiu_queue where type=%s and is_work=%s and fail_ip!=%s and fail_times<%s limit %s for update; """ # 更新SQL-拼字符串 update_sql = """ update hainiu_queue set is_work=1 where id in (%s); """ c_actions = [] # 用于装id,来更新 ids = [] db_util = DBUtil(_HAINIU_DB) try: # sql_params = [1, 0, _QUEUE_NEWS_FIND['MAX_FAIL_TIMES'], _QUEUE_NEWS_FIND['LIMIT_NUM']] # 屏蔽ip查询的参数 ip = Util().get_local_ip() sql_params = [ 1, 0, ip, _QUEUE_NEWS_FIND['MAX_FAIL_TIMES'], _QUEUE_NEWS_FIND['LIMIT_NUM'] ] # ({},{}) res1 = db_util.read_dict(select_sql, sql_params) for row in res1: id = row['id'] ids.append(str(id)) act = row['action'] params = row['params'] c_action = NewsFindConsumerAction(id, act, params) c_actions.append(c_action) if len(ids) > 0: db_util.execute_no_commit(update_sql % ",".join(ids)) db_util.commit() except Exception, e: db_util.rollback() traceback.print_exc(e)
def success_action(self, values): #1)记录种子url最后爬取成功数, (用来校验最后的爬取是否成功); #2)在hainiu_queue 表中删除已经爬取成功的url; seed_update_sql = """ update hainiu_web_seed set last_crawl_internally=%s,last_crawl_externally=%s,last_crawl_time=now() where md5=%s; """ queue_delete_sql = """ delete from hainiu_queue where id=%s """ db_util = DBUtil(_HAINIU_DB) try: sql_param = [values[1], values[2], values[3]] db_util.execute_no_commit(seed_update_sql, sql_param) sql_param = [values[0]] db_util.execute_no_commit(queue_delete_sql, sql_param) db_util.commit() except Exception, e: traceback.print_exc(e) db_util.rollback()
def success_action(self, values): ''' 消息动作处理成功之后,从队列中间件删除该消息,表示这个消息最终处理完成 :param values: 消息动作处理之后的结果 ''' delete_sql = """ delete from hainiu_queue where id=%s """ try: d = DBUtil(config._HAINIU_DB) id = values[0] sql = delete_sql % id d.execute(sql) except: self.rl.exception() self.rl.error(sql) d.rollback() finally: d.close()
def fail_action(self, values): # 每次失败都需要更新ip 和 失败次数 update_sql1=''' update web_queue set fail_ip = %s , fail_times = fail_times + 1 where id = %s; ''' # 当失败次数到达每台机器的最大重试次数,就将该记录的is_work=0 ,让其重试 update_sql2=''' update web_queue set is_work = 0 where id = %s; ''' # 更新seed表状态 update_seed_sql = ''' update web_seed set fail_times=fail_times + 1,fail_ip=%s where md5 =%s; ''' # 更新externally表状态 update_exter_sql = ''' update web_seed_externally set fail_times=fail_times + 1,fail_ip=%s where a_md5 =%s; ''' db_util = DBUtil(_ZZ_DB) try: id = values[0] ip = Util().get_local_ip() # 每次更新失败ip 和失败次数 # queue表 sql_params = [ip, id] db_util.execute_no_commit(update_sql1, sql_params) # seed 表 sql_params = [ip, values[1]] db_util.execute(update_seed_sql, sql_params) # externally表 db_util.execute(update_exter_sql, sql_params) if self.current_retry_num == _QUEUE_ZZ["C_RETRY_TIMES"] - 1: db_util.execute_no_commit(update_sql2 % id) db_util.commit() except Exception,err: db_util.rollback() traceback.print_exc(err)
def queue_items(self): # 屏蔽ip的查询方式 select_sql=''' select id, action, params from web_queue where type=%s and is_work=%s and fail_ip != %s and fail_times < %s limit 0, %s for update; ''' update_sql=''' update web_queue set is_work=1 where id in(%s); ''' db_util = DBUtil(_ZZ_DB) try: ip = Util().get_local_ip() sql_params = [1, 0, ip, _QUEUE_ZZ["MAX_FAIL_TIMES"], _QUEUE_ZZ["LIMIT_NUM"]] res = db_util.read_dict(select_sql, sql_params) actions = [] ids = [] for row in res: id = row["id"] ids.append(str(id)) action = row["action"] params = row["params"] # 封装对象 c_action = WebConsumerAction(id, action, params) actions.append(c_action) if len(actions) != 0: # 更新 is_work=1 db_util.execute_no_commit(update_sql % ",".join(ids)) db_util.commit() except Exception, err: actions = [] db_util.rollback() traceback.print_exc(err)
def success_action(self, values): delete_sql = """ delete from hainiu_queue where id=%s; """ update_hainiu_news_seed_sql = """ update hainiu_web_seed set last_crawl_internally=%s,last_crawl_externally=%s,last_crawl_time=now() where md5="%s";""" try: d = DBUtil(config._HAINIU_DB) id = values[5] sql = delete_sql % id d.execute_no_commit(sql) sql = update_hainiu_news_seed_sql % (values[3],values[4],values[0]) d.execute_no_commit(sql) d.commit() except: self.rl.exception() self.rl.error(sql) d.rollback() d.commit() finally: d.close()
def success_action(self, values): db_util = DBUtil(_HAINIU_DB) time_util = TimeUtil() #1)在hainiu_queue 表中删除已经下载成功的url; queue_delete_sql = """ delete from hainiu_queue where id=%s """ #2)更新内链表的最后更新时间; inner_update_sql = """ update hainiu_web_seed_internally set update_time= %s where a_md5=%s AND md5=%s """ update_time = time_util.get_timestamp() try: sql_param = [values[0]] db_util.execute_no_commit(queue_delete_sql, sql_param) sql_param = [update_time, values[1], values[2]] db_util.execute_no_commit(inner_update_sql, sql_param) db_util.commit() except Exception, e: traceback.print_exc(e) db_util.rollback()
def push_queue_items(): inert_sql = """ insert into hainiu_queue (type,params,action) values(1,%s,%s); """ count_sql = """ select count(1) from hainiu_queue where type=1; """ select_sql = """ select id from hainiu_queue where type=1 limit %s,%s; """ rl = LogUtil().get_base_logger() try: d = DBUtil(config._HAINIU_DB) sql = inert_sql insert_list = [("aaa", "bbb"), ("dffddf", "awwee")] d.executemany(sql, insert_list) sql = count_sql queue_total = d.read_one(sql)[0] print "queue_total", queue_total page_size = 10 page = (queue_total / page_size) + 1 print "page", page for i in range(0, page): sql = select_sql % (i * page_size, page_size) select_list = d.read_tuple(sql) print "page", i for record in select_list: id = record[0] print id except: rl.exception() rl.error(sql) d.rollback() finally: d.close()
def success_action(self, values): # 删除列表对应的记录 del_sql = ''' delete from web_queue where id =%s; ''' # 更新seed表状态 update_sql = ''' update web_seed set last_crawl_time=%s,last_crawl_internally=%s,last_crawl_externally=%s where md5 =%s; ''' db_util = DBUtil(_ZZ_DB) try: # 删除队列表 id = values[0] sql_param = [id] db_util.execute(del_sql, sql_param) # 更新seed表 # [(1574519076,), 95, 7, '824e29a21f2a02379f78b0675d1fc5eb'] sql_param =[values[2], values[3],values[4],values[1]] db_util.execute(update_sql, sql_param) except Exception, err: db_util.rollback() traceback.print_exc(err)
def create_seed(): sql = """ insert into web_seed (url,md5,domain,host,category,status) values ('%s','%s','%s','%s','%s',0); """ url = "https://news.sina.com.cn/" catetory = "新闻" hu = HtmlUtil() domain = get_tld(url) host = hu.get_url_host(url) u = Util() md5 = u.get_md5(url) rl = LogUtil().get_base_logger() try: d = DBUtil(config._ZZ_DB) sql = sql % (url, md5, domain, host, catetory) d.execute(sql) except: rl.exception() d.rollback() finally: d.close()
def create_seed(): url = "https://www.autohome.com.cn/all" catetory = "汽车" sql = """ insert into hainiu_web_seed (url,md5,domain,host,category,status) values ('%s','%s','%s','%s','%s',0); """ hu = HtmlUtil() domain = get_tld(url) host = hu.get_url_host(url) u = Util() md5 = u.get_md5(url) rl = LogUtil().get_base_logger() try: d = DBUtil(config._HAINIU_DB) sql = sql % (url, md5, domain, host, catetory) d.execute(sql) except: rl.exception() d.rollback() finally: d.close()
def xpath_config_file(): select_xpath_rule_sql = """select host,xpath,type from stream_extract_xpath_rule where host='%s' and status=0""" rl = LogUtil().get_base_logger() try: # _HAINIU_DB = {'HOST': '192.168.137.190', 'USER': '******', 'PASSWD': '12345678', 'DB': 'hainiucrawler', # 'CHARSET': 'utf8', 'PORT': 3306} d = DBUtil(config._HAINIU_DB) # d = DBUtil(_HAINIU_DB) r = redis.Redis('nn1.hadoop', 6379, db=6) # r = redis.Redis('redis.hadoop', 6379, db=6) f = FileUtil() t = TimeUtil() c = Client("http://nn1.hadoop:50070") time_str = t.now_time(format='%Y%m%d%H%M%S') # local_xpath_file_path = '/Users/leohe/Data/input/xpath_cache_file/xpath_file' + time_str local_xpath_file_path = '/home/qingniu/xpath_cache_file/xpath_file' + time_str start_cursor = 0 is_finish = True starttime = time.clock() host_set = set() while is_finish: values = set() limit = r.scan(start_cursor, 'total:*', 10) if limit[0] == 0: is_finish = False start_cursor = limit[0] for h in limit[1]: host = h.split(":")[1] total_key = h txpath_key = 'txpath:%s' % host fxpath_key = 'fxpath:%s' % host total = r.get(total_key) txpath = r.zrevrange(txpath_key, 0, 1) row_format = "%s\t%s\t%s\t%s" if txpath: # print 'txpath:%s' % txpath txpath_num = int(r.zscore(txpath_key, txpath[0])) if txpath.__len__() == 2: txpath_num_1 = int(r.zscore(txpath_key, txpath[1])) txpath_num_1 = txpath_num_1 if txpath_num_1 is not None else 0 # print 'txpath_max_num:%s' % txpath_num if txpath_num / float(total) >= 0.8: values.add(row_format % (host, txpath[0], 'true', '0')) host_set.add(host) else: if txpath_num >= 1: values.add(row_format % (host, txpath[0], 'true', '0')) host_set.add(host) if txpath_num_1 is not None and txpath_num_1 >= 1: values.add(row_format % (host, txpath[1], 'true', '0')) host_set.add(host) fxpath = r.smembers(fxpath_key) if fxpath: # print 'fxpath:%s' % fxpath for fx in fxpath: values.add(row_format % (host, fx, 'false', '0')) host_set.add(host) sql = select_xpath_rule_sql % host list_rule = d.read_tuple(sql) for rule in list_rule: type = rule[2] if type == 0: values.add(row_format % (rule[0], rule[1], 'true', '2')) host_set.add(host) elif type == 1: values.add(row_format % (rule[0], rule[1], 'false', '3')) host_set.add(host) f.write_file_line_pattern(local_xpath_file_path, values, "a") #上传到HDFS的XPATH配置文件目录 c.upload("/user/qingniu/xpath_cache_file/", local_xpath_file_path) endtime = time.clock() worksec = int(round((endtime - starttime))) rl.info('total host %s,action time %s\'s' % (host_set.__len__(), worksec)) except: rl.exception() d.rollback() finally: d.close()
def put_seed_to_queue(page_show_num): ''' 采用分页查询种子表数据,批量导入到hainiu_queue :param page_show_num: 一次查询条数 ''' # 统计hainiu_queue 未处理的记录数 select_queue_count_sql = """ select count(*) from hainiu_queue where type=%s and is_work=0 and fail_times=0; """ # 统计种子表符合条件的总记录数 select_seed_count_sql = """ select count(*) from hainiu_web_seed where status=0; """ # 分页查询种子表数据SQL select_seed_limit_sql = """ select url, md5, domain, host, category from hainiu_web_seed where status=0 limit %s,%s; """ # insert hainiu_queue sql insert_queue_sql = """ insert into hainiu_queue (type,action,params) values (%s, %s, %s); """ logger = LogUtil().get_logger("news_find_queue", "news_find_queue") db_util = DBUtil(_HAINIU_DB) try: #1) 统计hainiu_queue 未处理的记录数 sql_params = [1] # res1 是 () res1 = db_util.read_one(select_queue_count_sql, sql_params) queue_count = res1[0] if queue_count >= 5: logger.info("hainiu_queue 有 %d 条未处理的记录,不需要导入!" % queue_count) return None start_time = time.time() #2) 统计种子表符合条件的总记录数 res2 = db_util.read_one(select_seed_count_sql) seed_count = res2[0] # 计算有多少页 page_num = seed_count / page_show_num if seed_count % page_show_num == 0 \ else seed_count / page_show_num + 1 # 分页查询 for i in range(page_num): sql_params = [i * page_show_num, page_show_num] # ({},{},{},{},{}) res3 = db_util.read_dict(select_seed_limit_sql, sql_params) # 插入队列表的数据 insert_queue_values = [] params_dict = {} for row in res3: # url, md5, domain, host, category act = row['url'] md5 = row['md5'] domain = row['domain'] host = row['host'] category = row['category'] params_dict['md5'] = md5 params_dict['domain'] = domain params_dict['host'] = host params_dict['category'] = category params_json = json.dumps(params_dict, ensure_ascii=False, encoding='utf-8') insert_queue_values.append((1, act, params_json)) # 把查询的数据批量插入到队列表 db_util.executemany(insert_queue_sql, insert_queue_values) end_time = time.time() run_time = end_time - start_time logger.info("本地导入 %d 条数据, 用时 %.2f 秒" % (seed_count, run_time)) except Exception, e: logger.exception(e)
def put_queue_inner(): # 统计queue符合条件的记录数 count_queue_sql = ''' select count(*) from web_queue where is_work=%s and fail_times < %s; ''' # # 统计internally表的符合条件的总记录数 # count_inner_sql=''' # select count(*) from web_seed_internally where status=0; # ''' # # # web_seed_internally 表的记录 # select_inner_limit_sql=''' # select id,a_url,param from web_seed_internally where status=0 limit %s,%s; # ''' # 插入queue表记录 insert_queue_sql = ''' insert into web_queue (type,action,params) values(%s,%s,%s); ''' # web_seed_internally status update_sql = ''' update web_seed_internally set status=1 where md5=%s and a_md5=%s; ''' try: # redis_tmp 数据 redis_d = RedisUtill() db_uitl = DBUtil(_ZZ_DB) ips = ['192.168.235.136', '192.168.235.137', '192.168.235.138'] port = '6379' list = [] total_num = 0 is_get_lock = redis_d.get_lock('seed_lock', 10) logger = LogUtil().get_base_logger() sql_params = [0, _QUEUE_ZZ['MAX_FAIL_TIMES']] res1 = db_uitl.read_one(count_queue_sql, sql_params) total_num1 = res1[0] if total_num1 != 0: logger.info("queue has %d records,not insert!" % total_num1) return None logger.info("正在获取锁...") if is_get_lock: logger.info("获取到锁") start_time = time.time() def scan_limit_to_queue_table(host, port, cursor, match, count): r = redis.Redis(host, port) rs = r.scan(cursor, match, count) # 新游标 next_num = rs[0] # print rs li = rs[1] for i in li: if i.__contains__('a_url'): list.append(i) # 递归出口 if next_num == 0: return None scan_limit_to_queue_table(host, port, next_num, match, count) for ip in ips: scan_limit_to_queue_table(ip, port, 0, 'seed_temp*', 100) # 分页插入queue表 redis_result = [] up_inner = [] delete_list = [] for k in list: if k.__contains__('a_url'): # 确定同一MD5的其他参数的key param = k.replace('a_url', 'param') md5 = k.replace('a_url', 'md5') a_md5 = k.replace('a_url', 'a_md5') action = redis_d.get_value_for_key(k) params = redis_d.get_value_for_key(param) redis_result.append((2, action, params)) md5_val = redis_d.get_value_for_key(md5) a_md5_val = redis_d.get_value_for_key(a_md5) up_inner.append((md5_val, a_md5_val)) # 添加要删除的列表 delete_list.append(k) delete_list.append(param) delete_list.append(md5) delete_list.append(a_md5) total_num += 1 # 批量插入queue if (len(redis_result) == 5): db_uitl.executemany(insert_queue_sql, redis_result) db_uitl.executemany(update_sql, up_inner) redis_result = [] up_inner = [] # 提交不满五个的最后一组 db_uitl.executemany(insert_queue_sql, redis_result) db_uitl.executemany(update_sql, up_inner) # 删除redis_tmp redis_d.delete_batch(delete_list) redis_d.release('seed_lock') logger.info("释放锁") else: logger.info('其他线程正在处理,获取锁超过最大超时时间,退出处理逻辑 ') end_time = time.time() run_time = end_time - start_time logger.info("total_num:%d, run_time:%.2f" % (total_num, run_time)) except Exception, err: db_uitl.rollback() redis_d.release('seed_lock') traceback.print_exc(err)
def put_queue_inner(): # 统计queue符合条件的记录数 count_queue_sql = ''' select count(*) from web_queue where is_work=%s and fail_times < %s; ''' # 统计internally表的符合条件的总记录数 count_inner_sql = ''' select count(*) from web_seed_internally where status=0; ''' # web_seed_internally 表的记录 select_inner_limit_sql = ''' select id,a_url,param from web_seed_internally where status=0 limit %s,%s; ''' # 插入queue表记录 insert_queue_sql = ''' insert into web_queue (type,action,params) values(%s,%s,%s); ''' # web_seed_internally status update_sql = ''' update web_seed_internally set status=1 where id in(%s); ''' db_uitl = DBUtil(_ZZ_DB) try: sql_params = [0, _QUEUE_ZZ['MAX_FAIL_TIMES']] res1 = db_uitl.read_one(count_queue_sql, sql_params) total_num1 = res1[0] if total_num1 != 0: print "queue has %d records,not insert!" % total_num1 return None start_time = time.time() res2 = db_uitl.read_one(count_inner_sql) total_num2 = res2[0] # 计算分多少页查询 page_num = total_num2 / _QUEUE_ZZ[ "LIMIT_NUM"] if total_num2 % _QUEUE_ZZ[ "LIMIT_NUM"] == 0 else total_num2 / _QUEUE_ZZ["LIMIT_NUM"] + 1 # 分页插入queue表 ids = [] for i in range(0, page_num): sql_params = [i * _QUEUE_ZZ["LIMIT_NUM"], _QUEUE_ZZ["LIMIT_NUM"]] res3 = db_uitl.read_dict(select_inner_limit_sql, sql_params) list1 = [] for row in res3: id = row["id"] ids.append(str(id)) action = row["a_url"] params1 = row["param"] type = 2 list1.append((type, action, params1)) # 批量插入queue db_uitl.executemany(insert_queue_sql, list1) # 更新status = 1 db_uitl.execute(update_sql % ",".join(ids)) db_uitl.commit() end_time = time.time() run_time = end_time - start_time print "total_num:%d, run_time:%.2f" % (total_num2, run_time) except Exception, err: db_uitl.rollback() traceback.print_exc(err)
#-*- encoding: utf-8 -*- ''' db_test.py Created on 2019/6/25 11:14 Copyright (c) 2019/6/25, 海牛学院版权所有. @author: 潘牛 ''' from commons.util.db_util import DBUtil from configs.config import _HAINIU_DB db_util = DBUtil(_HAINIU_DB) # 设置字符集是utf8mb4 db_util.execute_no_commit("set NAMES utf8mb4;") # 测试 execute(self,sql,params = None): # sql = """ # insert into hainiu_queue (type,action,params) values (1, 'www.hainiubl.com', 'aa'); # """ # db_util.execute(sql) # 字符串拼接(不推荐用) # sql = """ # insert into hainiu_queue (type,action,params) values (%d, '%s', '%s'); # """ % (1, 'www.hainiubl.com', 'aa') # db_util.execute(sql) # ------------------------------------- # 测试 execute(self,sql,params != None): # sql占位符(推荐用法) # sql = """
def redis2Hdfs(): select_xpath_rule_sql = """select host,xpath,type from stream_extract_xpath_rule where host='%s' and status=0""" rl = LogUtil().get_base_logger() try: d = DBUtil(_ZZ_DB) start = 0 is_finish = True host_set = set() f = FileUtil() t = TimeUtil() time_str = t.now_time(format='%Y%m%d%H%M%S') #local_xpath_file_path = '/user/zengqingyong17/spark/xpath_cache_file' + time_str local_xpath_file_path = 'E:/python_workspaces/data/xpath/xpath_file' + time_str starttime = time.clock() r = redis.Redis('nn1.hadoop', '6379', db=6) while is_finish: values = set() rs = r.scan(start, "total_z:*", 10) # 新游标 start = rs[0] if start ==0: is_finish = False # print rs for i in rs[1]: host = i.split(":")[1] total_key = i txpath_key = 'txpath_z:%s' % host fxpath_key = 'fxpath_z:%s' % host total = r.get(total_key) # 降序排序获得次数(0,1) txpath = r.zrevrange(txpath_key, 0, 1) row_format = "%s\t%s\t%s\t%s" if txpath: txpath_num = int(r.zscore(txpath_key, txpath[0])) if txpath.__len__() == 2: # 返回txpath_key 中txpath[1]的数值 txpath_num_1 = int(r.zscore(txpath_key, txpath[1])) txpath_num_1 = txpath_num_1 if txpath_num_1 is not None else 0 if txpath_num / float(total) >= 0.8: values.add(row_format % (host, txpath[0], 'true', '0')) host_set.add(host) else: if txpath_num >= 100: values.add(row_format % (host, txpath[0], 'true', '0')) host_set.add(host) if txpath_num_1 is not None and txpath_num_1 >= 100: values.add(row_format % (host, txpath[1], 'true', '0')) host_set.add(host) # 获得fxpath_key的全部值 fxpath = r.smembers(fxpath_key) if fxpath: # print 'fxpath:%s' % fxpath for fx in fxpath: values.add(row_format % (host, fx, 'false', '1')) host_set.add(host) sql = select_xpath_rule_sql % host list_rule = d.read_tuple(sql) for rule in list_rule: type = rule[2] if type == 0: values.add(row_format % (rule[0], rule[1], 'true', '2')) host_set.add(host) elif type == 1: values.add(row_format % (rule[0], rule[1], 'false', '3')) host_set.add(host) f.write_file_line_pattern(local_xpath_file_path, values, "a") #上传到HDFS的XPATH配置文件目录 # c.upload("/user/qingniu/xpath_cache_file/", local_xpath_file_path) endtime = time.clock() worksec = int(round((endtime - starttime))) rl.info('total host %s,action time %s\'s' % (host_set.__len__(), worksec)) except: rl.exception() d.rollback() finally: d.close()
def action(self): is_success = True t = TimeUtil() u = Util() hu = HtmlUtil() r = RequestUtil() in_values = [] ex_values = [] a_href = '' main_md5 = u.get_md5(self.url) now_time = datetime.now() update_time = int(time.mktime(now_time.timetuple())) create_time = update_time create_day = int(t.now_day().replace('-', '')) create_hour = int(t.now_hour()) try: html = r.http_get_phandomjs(self.url) domain = get_tld(self.url) soup = BeautifulSoup(html, 'lxml') a_docs = soup.find_all("a") a_set = set() a_param = {} out_json_srt = '' status = 0 host = hu.get_url_host(self.url) for a in a_docs: a_href = self.get_format_url(a,host) a_title = a.get_text().strip() if a_href == '' or a_title == '': continue if a_set.__contains__(a_href): continue a_set.add(a_href) req = urllib2.Request(url=a_href) a_host = req.get_host() if req.get_host() is not None else '' a_md5 = u.get_md5(a_href) if a_title != '': a_param['title'] = a_title out_json_srt = json.dumps(a_param,ensure_ascii=False) a_xpath = hu.get_dom_parent_xpath_js(a) insert_values = (main_md5,domain,host,a_md5,a_host,a_xpath,create_time,create_day,create_hour,update_time,status, MySQLdb.escape_string(self.url), MySQLdb.escape_string(a_href), MySQLdb.escape_string(a_title), out_json_srt) if a_host.__contains__(domain): in_values.append(insert_values) else: ex_values.append(insert_values) in_table = 'hainiu_web_seed_internally' ex_table = 'hainiu_web_seed_externally' insert_sql = """ insert into <table> (md5,domain,host,a_md5,a_host,a_xpath,create_time,create_day,create_hour,update_time,status,url,a_url,a_title,param) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE update_time=update_time; """ try: d = DBUtil(config._HAINIU_DB) d.execute_no_commit("set NAMES utf8mb4;") if in_values.__len__() != 0: sql = insert_sql.replace('<table>',in_table) d.executemany_no_commit(sql,in_values) if ex_values.__len__() != 0: sql = insert_sql.replace('<table>',ex_table) d.executemany_no_commit(sql,ex_values) d.commit() except: is_success = False self.rl.exception() self.rl.error(sql) d.rollback() finally: d.close() except: is_success = False self.rl.exception() finally: r.close_phandomjs() return super(self.__class__, self).result(is_success, [main_md5,self.url,a_href,in_values.__len__(),ex_values.__len__(),self.queue_id])
def put_seed(): # 统计seed符合条件的记录数 count_queue_sql = ''' select count(*) from web_seed where status=%s and fail_times < %s; ''' # 统计web_seed表的符合条件的总记录数 count_exter_sql = ''' select count(*) from web_seed_externally where status=0; ''' # web_seed_externally 表的记录 select_exter_limit_sql = ''' select id,a_url,a_md5,a_host,param from web_seed_externally where status=0 limit %s,%s; ''' # 插入seed表记录 insert_seed_sql = ''' insert into web_seed (url,md5,domain,host,category) values (%s,%s,%s,%s,%s); ''' # web_seed_internally status update_sql = ''' update web_seed_externally set status=1 where id in(%s); ''' db_uitl = DBUtil(_ZZ_DB) sql_params = [0, _QUEUE_ZZ['MAX_FAIL_TIMES']] res1 = db_uitl.read_one(count_queue_sql, sql_params) total_num1 = res1[0] if total_num1 != 0: print "queue has %d records,not insert!" % total_num1 return None start_time = time.time() res2 = db_uitl.read_one(count_exter_sql) total_num2 = res2[0] # 计算分多少页查询 page_num = total_num2 / _QUEUE_ZZ["LIMIT_NUM"] if total_num2 % _QUEUE_ZZ[ "LIMIT_NUM"] == 0 else total_num2 / _QUEUE_ZZ["LIMIT_NUM"] + 1 # hu = HtmlUtil() # u = Util() # 分页插入queue表 try: ids = [] for i in range(0, page_num): sql_params = [i * _QUEUE_ZZ["LIMIT_NUM"], _QUEUE_ZZ["LIMIT_NUM"]] res3 = db_uitl.read_dict(select_exter_limit_sql, sql_params) list1 = [] for row in res3: id = row["id"] ids.append(str(id)) url = row["a_url"] domain = get_tld(url) # host = hu.get_url_host(url) # md5 = u.get_md5(url) host = row["a_host"] md5 = row["a_md5"] category = row["param"] list1.append((url, md5, domain, host, category)) # 批量插入queue db_uitl.executemany(insert_seed_sql, list1) # 更新status = 1 db_uitl.execute(update_sql % ",".join(ids)) except Exception, err: db_uitl.rollback() traceback.print_exc(err)
def put_queue(page_show_num): db_util = DBUtil(_ZZ_DB) # 统计queue符合条件的记录数 count_queue_sql = ''' select count(*) from web_queue where is_work=%s and fail_times < %s; ''' # 统计web_seed表的符合条件的总记录数 count_seed_sql = ''' select count(*) from web_seed where status=0; ''' # 分页查询web_seed 表的记录 select_seed_limit_sql = ''' select id,url,category from web_seed where status=0 limit %s,%s; ''' # 插入queue表记录 insert_queue_sql = ''' insert into web_queue (type,action,params) values(%s,%s,%s); ''' # 更新web_seed表中的 status update_sql = ''' update web_seed set status=1 where id in(%s); ''' try: sql_params = [0, _QUEUE_ZZ["MAX_FAIL_TIMES"]] res1 = db_util.read_one(count_queue_sql, sql_params) total_num1 = res1[0] if total_num1 != 0: print "queue has %d records,not insert!" % total_num1 return None start_time = time.time() # 统计web_seed 表符合条件的总记录数 res2 = db_util.read_one(count_seed_sql) total_num2 = res2[0] # 计算分多少页查询 page_num = total_num2 / page_show_num if total_num2 % page_show_num == 0 else total_num2 / page_show_num + 1 # 分页查询 ids = [] for i in range(0, page_num): sql_params = [i * page_show_num, page_show_num] print sql_params res3 = db_util.read_dict(select_seed_limit_sql, sql_params) list1 = [] for row in res3: id = row["id"] ids.append(str(id)) action = row["url"] params = row["category"] type = 1 list1.append((type, action, params)) # 批量插入queue db_util.executemany(insert_queue_sql, list1) # 更新 status=1 db_util.execute_no_commit(update_sql % ",".join(ids)) db_util.commit() end_time = time.time() run_time = end_time - start_time print "total_num:%d, run_time:%.2f" % (total_num2, run_time) except Exception, err: db_util.rollback() traceback.print_exc(err)
def action(self): #爬取 hainiu_queue 中符合要求的url 请求页面的所有 a标签url r = RequestUtil() hu = HtmlUtil() u = Util() # is_success = True db_util = DBUtil(_HAINIU_DB) time_util = TimeUtil() # 内外链表的列表 inner_list = [] exter_list = [] #获取种子的md5 md5 = u.get_md5(self.act) try: # 通过phandomjs 请求url,返回网页,包括网页的ajax请求 html = r.http_get_phandomjs(self.act) #可以从HTML或XML文件中提取数据的Python第三方库 soup = BeautifulSoup(html, 'lxml') # a链接dom对象列表 a_docs = soup.find_all("a") if len(a_docs) == 0: is_success = False aset = set() #获取种子的domain domain = hu.get_url_domain(self.act) #获取种子的host host = hu.get_url_host(self.act) # 时间(create_time、create_day、create_hour、update_time) # create_time=time_util.get_timestamp() # # create_day = int(time_util.now_day().replace('-', '')) # create_hour=int(time_util.now_hour()) # update_time=create_time create_time = time_util.get_timestamp() # 获取年月日格式 create_day = int(time_util.now_day(format='%Y%m%d')) # 获取小时 create_hour = int(time_util.now_hour()) update_time = create_time # params_json = json.dumps(self.params, ensure_ascii=False, encoding='utf-8') for a_doc in a_docs: #获取a标签的href a_href = hu.get_format_url(self.act, a_doc, host) #获取a标签的内容 a_title = a_doc.get_text().strip() if a_href == '' or a_title == '': continue if aset.__contains__(a_href): continue aset.add(a_href) #获取a标签的host a_host = hu.get_url_host(a_href) #获取a标签href链接url的md5 a_md5 = u.get_md5(a_href) #获取a标签所对应的xpath a_xpath = hu.get_dom_parent_xpath_js_new(a_doc) # 一行数据 row_data = (self.act, md5, self.params, domain, host, a_href, a_md5, a_host, a_xpath, a_title, create_time, create_day, create_hour, update_time) if a_href.__contains__(domain): inner_list.append(row_data) else: exter_list.append(row_data) # 并解析存入内链表或外链表,在存入时,如果url已存在,只做 # update 操作。(保证链接页面不会重复爬取) if len(inner_list) > 0: inner_insert_sql = """ insert into hainiu_web_seed_internally (url,md5,param,domain,host,a_url,a_md5,a_host,a_xpath,a_title,create_time, create_day,create_hour,update_time) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE update_time=values(update_time); """ db_util.executemany_no_commit(inner_insert_sql, inner_list) if len(exter_list) > 0: exter_insert_sql = """ insert into hainiu_web_seed_externally (url,md5,param,domain,host,a_url,a_md5,a_host,a_xpath,a_title,create_time, create_day,create_hour,update_time) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE update_time=values(update_time); """ db_util.executemany_no_commit(exter_insert_sql, exter_list) db_util.commit() except Exception, e: is_success = False db_util.rollback() traceback.print_exc(e)