def update_score():
# 连接mysql
config = {
'host': '192.168.1.118',
'port': 3306,
'user': '******',
'passwd': 'poms@db',
'db': 'mymonitor',
'charset': 'utf8mb4',
'cursorclass': pymysql.cursors.DictCursor
}
# 1.查询操作
# 编写sql 查询语句
select_sql = "select Column_Link_ID,URL,Title from column_link where Level_Score is null limit 1000;"
update_sql_pattern = "UPDATE column_link SET " \
"Level_Score = CASE Column_Link_ID {0} END, " \
"Score_Detail = CASE Column_Link_ID {1} END " \
"WHERE Column_Link_ID IN {2};"
when_then_score_pattern = " WHEN {} THEN {} "
when_then_detail_pattern = " WHEN {} THEN '{}' "
id_list = []
try:
results = common.query_mysql(config, select_sql) # 获取查询的所有记录
print(results)
when_then_score = ""
when_then_detail = ""
# 遍历结果
for row in results:
Column_Link_ID = row['Column_Link_ID']
url = row['URL']
title = row['Title']
level_score, status = common.is_need_filter(title, url, False)
# print(level_score, status)
when_then_score = when_then_score + when_then_score_pattern.format(
Column_Link_ID, level_score)
when_then_detail = when_then_detail + when_then_detail_pattern.format(
Column_Link_ID, status)
id_list.append(Column_Link_ID)
id_tuple = tuple(id_list)
sql = update_sql_pattern.format(when_then_score, when_then_detail,
id_tuple)
print(sql)
try:
common.query_mysql(config, sql)
except Exception as e:
print(e)
except Exception as e:
print(e)
def main(start, end):
extractor_116 = {
'host': '192.168.1.116',
'port': 3306,
'user': '******',
'passwd': 'poms@db',
'db': 'mymonitor'
}
extractor_118 = {
'host': '192.168.1.118',
'port': 3306,
'user': '******',
'passwd': 'poms@db',
'db': 'datasource'
}
select_column = f"select Website_No,ListPage_URL,ListPage_Title from listpage_url where ListPage_URL_ID IN " \
f"(select ListPage_URL_ID from cloud_listpage_url where cloud_listpage_url_id BETWEEN {start} AND {end});"
try:
results = common.query_mysql(extractor_116, select_column)
column_list = []
for item in results:
URL = item['ListPage_URL']
Title = item['ListPage_Title']
Website_No = item['Website_No']
Domain_Code = common.get_domain_code(URL)
Host_Code = common.get_host_code(URL)
# 计算url MD5 先去掉http和末尾斜杆
md5_source = URL
md5_source = md5_source.replace('http://', '')
md5_source = md5_source.replace('https://', '')
md5_source = md5_source.rstrip('/')
Record_MD5_ID = common.get_token(md5_source)
Level_Score, Score_Detail = common.is_need_filter(Title, URL)
Column_Extraction_Deep = 1
column = f"({Column_Extraction_Deep}, '{URL}', '{Title}', '{Domain_Code}', '{Host_Code}', '{Record_MD5_ID}', {Level_Score}, '{Score_Detail}', '{Website_No}')"
if Level_Score > 20:
column_list.append(column)
# 批量插入
values = ",".join(column_list)
insert_column = f"insert ignore into column_link(Column_Extraction_Deep, URL, Title, Domain_Code, Host_Code, Record_MD5_ID, Level_Score, Score_Detail, Website_No) values{values};"
# print(insert_column)
common.query_mysql(extractor_118, insert_column)
except Exception as e:
print(e)
def main():
extractor_118 = {
'host': '192.168.1.118',
'port': 3306,
'user': '******',
'passwd': 'poms@db',
'db': 'datasource'
}
select_column = f"select * from website where ID>1577;"
try:
results = common.query_mysql(extractor_118, select_column)
column_list = []
for item in results:
ID = item['ID']
Column_Extraction_Deep = '0'
URL = item['URL']
Title = item['Title']
Domain_Code = common.get_domain_code(URL)
Host_Code = common.get_host_code(URL)
# 计算url MD5 先去掉http和末尾斜杆
md5_source = URL
md5_source = md5_source.replace('http://', '')
md5_source = md5_source.replace('https://', '')
md5_source = md5_source.rstrip('/')
Record_MD5_ID = common.get_token(md5_source)
Level_Score = '100'
Score_Detail = '{"status": True, "message": "root page"}'
Website_No = 'OVERSEA'
column = f"({Column_Extraction_Deep}, '{URL}', '{Title}', '{Domain_Code}', '{Host_Code}', '{Record_MD5_ID}', {Level_Score}, '{Score_Detail}', '{Website_No}')"
column_list.append(column)
# # 更新md5
update_website = f"update website set record_md5_id='{Record_MD5_ID}' where ID={ID}"
common.query_mysql(extractor_118, update_website)
insert_column = f"insert ignore into column_link_oversea(Column_Extraction_Deep, URL, Title, Domain_Code, Host_Code, Record_MD5_ID, Level_Score, Score_Detail, Website_No) values{column};"
print(insert_column)
common.query_mysql(extractor_118, insert_column)
# # 批量插入
# values = ",".join(column_list)
# insert_column = f"insert ignore into column_link_oversea(Column_Extraction_Deep, URL, Title, Domain_Code, Host_Code, Record_MD5_ID, Level_Score, Score_Detail, Website_No) values{values};"
# print(insert_column)
# common.query_mysql(extractor_118, insert_column)
except Exception as e:
print(e)
def main():
extractor_116 = {'host': '192.168.1.116', 'port': 3306, 'user': '******', 'passwd': 'poms@db', 'db': 'mymonitor'}
extractor_118 = {'host': '192.168.1.118', 'port': 3306, 'user': '******', 'passwd': 'poms@db', 'db': 'datasource'}
select_column = f"""select ad.Article_URL, ad.Domain_Code, ad.Article_Source from article_detail ad
where 1=1 and ad.Website_No in (select Website_No from website where Website_Name like'%百度新闻%')
and ad.Extracted_Time>'2020-10-01' and ad.Extracted_Time<'2020-11-25'
and Article_Source is not NULL and Article_Source !='' GROUP BY Domain_Code;"""
try:
results = common.query_mysql(extractor_116, select_column)
except Exception as e:
results = []
column_list = []
for i in results:
title = i['Article_Source']
listpage_url = i['Article_URL']
domain_code = common.get_domain_code(listpage_url)
host_code = common.get_host_code(listpage_url)
host_code_index = listpage_url.index(host_code)+len(host_code)
listpage_url = listpage_url[0:host_code_index] + '/'
# 计算url MD5 先去掉http和末尾斜杆
md5_source = listpage_url
md5_source = md5_source.replace('http://', '')
md5_source = md5_source.replace('https://', '')
md5_source = md5_source.rstrip('/')
record_md5_id = common.get_token(md5_source)
level_score = '100'
Score_Detail = '"{\"status\": True, \"message\": \"root page\"}"'
website_no = 'BAIDU_NEWS'
column_extraction_deep = 0
column = f"('{title}', '{listpage_url}', '{record_md5_id}', '{website_no}', {column_extraction_deep}, '{domain_code}', '{host_code}', '{level_score}', '{Score_Detail}')"
# column_list.append(column)
print(column)
# 批量插入
values = column
insert_column = f"replace into column_link(Title, URL, record_md5_id, website_no, column_extraction_deep, domain_code, host_code, level_score, Score_Detail) values{values};"
# print(insert_column)
try:
common.query_mysql(extractor_118, insert_column)
except Exception as e:
pass
def update_host_code():
# 连接mysql
config = {
'host': '192.168.1.118',
'port': 3306,
'user': '******',
'passwd': 'poms@db',
'db': 'mymonitor',
'charset': 'utf8mb4',
'cursorclass': pymysql.cursors.DictCursor
}
# 1.查询操作
# 编写sql 查询语句
select_sql = "select Column_Link_ID,URL from column_link where host_code is null limit 1000;"
# update_sql = "update column_link set host_code='{}' where Column_Link_ID={};"
update_sql_pattern = "UPDATE column_link SET host_code = CASE Column_Link_ID {} END WHERE Column_Link_ID IN {};"
when_then_pattern = " WHEN {} THEN '{}' "
id_list = []
try:
results = common.query_mysql(config, select_sql) # 获取查询的所有记录
when_then = ""
# 遍历结果
for row in results:
Column_Link_ID = row['Column_Link_ID']
url = row['URL']
host_code = common.get_host_code(url)
if len(host_code) > 50:
continue
# sql = update_sql.format(host_code, Column_Link_ID)
when_then = when_then + when_then_pattern.format(
Column_Link_ID, host_code)
id_list.append(Column_Link_ID)
id_tuple = tuple(id_list)
sql = update_sql_pattern.format(when_then, id_tuple)
print(sql)
try:
common.query_mysql(config, sql)
except Exception as e:
print(e)
except Exception as e:
print(e)
def main(start, end):
extractor_116 = {'host': '192.168.1.116', 'port': 3306, 'user': '******', 'passwd': 'poms@db', 'db': 'mymonitor'}
extractor_118 = {'host': '192.168.1.118', 'port': 3306, 'user': '******', 'passwd': 'poms@db', 'db': 'datasource'}
# 每次读取100万，并按domain_code排序，这样分配节点时便于把域名相同的分在一个组
select_column = f"select title,url,domain_code,record_md5_id from column_link where Column_Link_ID BETWEEN {start} AND {end};"
try:
results = common.query_mysql(extractor_118, select_column)
column_list = []
count = 0
print('len(results): ', len(results))
for item in results:
Cloud_Server_ID = random.randint(10000, 10062)
Listpage_URL = item['url']
# 过滤空格换行等
p = re.compile('\s+')
Listpage_URL = re.sub(p, '', Listpage_URL)
ListPage_Title = item['title']
Domain_Code = item['domain_code']
Record_MD5_ID = item['record_md5_id']
column = f"({Cloud_Server_ID}, '{Listpage_URL}', '{ListPage_Title}', '{Domain_Code}', '{Record_MD5_ID}')"
column_list.append(column)
# 每1000条插入一次，分批
if count > 0 and not count % 1000:
print(count)
values = ",".join(column_list)
insert_column = f"insert ignore into cloud_listpage_url(Cloud_Server_ID, Listpage_URL, ListPage_Title, Domain_Code, Record_MD5_ID) values{values};"
# print(insert_column)
common.query_mysql(extractor_116, insert_column)
column_list = []
count += 1
print('len(column_list): ', len(column_list))
# 批量插入
values = ",".join(column_list)
insert_column = f"insert ignore into cloud_listpage_url(Cloud_Server_ID, Listpage_URL, ListPage_Title, Domain_Code, Record_MD5_ID) values{values};"
# print(insert_column)
common.query_mysql(extractor_116, insert_column)
except Exception as e:
print(e)
def create_task(loop, database_config):
semaphore = asyncio.Semaphore(200) # 限制并发量为500
try:
tasks = []
# 查询待采集目标
select_column = f"""
select Column_Link_ID, Column_Extraction_Deep, URL, Domain_Code, Website_No, Extracted_flag
from column_link where Extracted_flag is null
ORDER BY Column_Extraction_Deep limit 200;
"""
print('=====query new tasks=====')
target_items = common.query_mysql(database_config, select_column)
print('=====start tasks=====')
id_list = [0]
for item in target_items:
id_list.append(item["Column_Link_ID"])
url = item["URL"]
domain_code = item["Domain_Code"]
website_no = item["Website_No"]
column_extraction_deep = item["Column_Extraction_Deep"]
# 最多采集3层
if column_extraction_deep <= 3:
task = asyncio.ensure_future(
get_response(database_config, semaphore, url,
column_extraction_deep, domain_code,
website_no))
tasks.append(task)
results = loop.run_until_complete(asyncio.gather(*tasks))
# print(results)
print('=====finish tasks=====')
# 更新Extracted_flag
id_list = tuple(id_list)
update_flag = f"update column_link set Extracted_flag='S' where Column_Link_ID in {id_list};"
print('=====update flag=====')
common.query_mysql(database_config, update_flag)
return len(target_items)
except Exception as e:
if len(str(e)) > 0:
logger.error(str(e))
return None
def main():
extractor_116 = {
'host': '192.168.1.116',
'port': 3306,
'user': '******',
'passwd': 'poms@db',
'db': 'mymonitor'
}
# ID每增长1万个，编入同一个网站编号
insert_website = '''
insert into cloud_task_schedule(Cloud_Server_Count,cloud_server_id,ListPage_URL_Count)
select 63,cloud_server_id,count(1) from cloud_listpage_url
GROUP BY cloud_server_id order by cloud_server_id;
'''
print(insert_website)
try:
common.query_mysql(extractor_116, insert_website)
except Exception as e:
print(e)
def main(start_server_id, end_server_id, count):
extractor_116 = {
'host': '192.168.1.116',
'port': 3306,
'user': '******',
'passwd': 'poms@db',
'db': 'mymonitor'
}
for i in range(start_server_id, end_server_id):
print(i)
update_website = f'''
update cloud_task_schedule set cloud_server_id={i} where 1=1
and schedule_id in (select * from
(select Schedule_ID from cloud_task_schedule where 1=1 and Cloud_Server_ID is null order by Schedule_ID limit {count})aa
);
'''
print(update_website)
try:
common.query_mysql(extractor_116, update_website)
except Exception as e:
print(e)
def main():
extractor_116 = {
'host': '192.168.1.116',
'port': 3306,
'user': '******',
'passwd': 'poms@db',
'db': 'mymonitor'
}
select_sql = 'select cloud_server_id,website_no from cloud_task_schedule;'
try:
results = common.query_mysql(extractor_116, select_sql)
for item in results:
cloud_server_id = item['cloud_server_id']
website_no = item['website_no']
print(cloud_server_id, website_no)
update_cloud = f"update cloud_listpage_url set Cloud_Server_ID={cloud_server_id} where website_no='{website_no}';"
print(update_cloud)
common.query_mysql(extractor_116, update_cloud)
except Exception as e:
print(e)
def main(start, end):
extractor_133 = {'host': '192.168.1.133', 'port': 3306, 'user': '******', 'passwd': 'poms@db', 'db': 'mymonitor'}
extractor_118 = {'host': '192.168.1.118', 'port': 3306, 'user': '******', 'passwd': 'poms@db', 'db': 'datasource'}
select_column = f"select * from column_link_2 where Column_Link_ID BETWEEN {start} AND {end};"
try:
results = common.query_mysql(extractor_118, select_column)
column_list = []
for item in results:
Extracted_flag = item['Extracted_flag']
Column_Extraction_Deep = item['Column_Extraction_Deep']
URL = item['URL']
Title = item['Title']
Domain_Code = item['Domain_Code']
Host_Code = item['Host_Code']
# 计算url MD5 先去掉http和末尾斜杆
md5_source = URL
md5_source = md5_source.replace('http://', '')
md5_source = md5_source.replace('https://', '')
md5_source = md5_source.rstrip('/')
Record_MD5_ID = common.get_token(md5_source)
Level_Score = item['Level_Score']
Score_Detail = item['Score_Detail']
Website_No = item['Website_No']
Is_User_Added = item['Is_User_Added']
column = f"('{Extracted_flag}', {Column_Extraction_Deep}, '{URL}', '{Title}', '{Domain_Code}', '{Host_Code}', '{Record_MD5_ID}', {Level_Score}, '{Score_Detail}', '{Website_No}', {Is_User_Added})"
column_list.append(column)
# 批量插入
values = ",".join(column_list)
insert_column = f"insert ignore into column_link(Extracted_flag, Column_Extraction_Deep, URL, Title, Domain_Code, Host_Code, Record_MD5_ID, Level_Score, Score_Detail, Website_No, Is_User_Added) values{values};"
# print(insert_column)
common.query_mysql(extractor_118, insert_column)
except Exception as e:
print(e)
def main(website_no, start, end):
extractor_116 = {
'host': '192.168.1.116',
'port': 3306,
'user': '******',
'passwd': 'poms@db',
'db': 'mymonitor'
}
# ID每增长1万个，编入同一个网站编号
update_website = f"update cloud_listpage_url set Website_No='{website_no}' where Cloud_Listpage_URL_ID BETWEEN {start} AND {end};"
print(update_website)
try:
result = common.query_mysql(extractor_116, update_website)
print(result)
except Exception as e:
print(e)
def parse_html_to_database(database_config, url, column_extraction_deep,
domain_code_source, website_no, Is_Need_VPN, text):
try:
root = etree.HTML(text, parser=etree.HTMLParser(encoding='utf-8'))
column_extraction_deep = int(column_extraction_deep) + 1
items = root.xpath('//a')
column_list = []
for num, item in enumerate(items):
title = "".join(item.xpath('.//text()'))
listpage_url = "".join(item.xpath('./@href'))
listpage_url = urljoin(url, listpage_url)
# 去掉标点符号
title = common.filter_punctuation(title)
listpage_url = common.match_url(listpage_url)
# 计算url MD5 先去掉http和末尾斜杆
md5_source = listpage_url
md5_source = md5_source.replace('http://', '')
md5_source = md5_source.replace('https://', '')
md5_source = md5_source.rstrip('/')
record_md5_id = common.get_token(md5_source)
domain_code = common.get_domain_code(listpage_url)
host_code = common.get_host_code(listpage_url)
# domain 要与源域名一致
if domain_code_source != domain_code:
continue
# 计算a节点占全部a节点百分比，如果总节点小于50就去前50%的节点；如果在50和200之前，就取前30%；大于200，就取前20%
len_items = len(items)
node_percent = num / len(items)
# print(num, 'percent:{:.0%}'.format(node_percent), title)
logger.info(
str(num) + 'percent:{:.0%}'.format(node_percent) + title)
if len_items < 50:
if node_percent > 0.5:
continue
if (len_items >= 50) and (len_items <= 200):
if node_percent > 0.3:
continue
if len_items > 200:
if node_percent > 0.2:
continue
# 垃圾词、垃圾域名过滤
level_score, score_detail = common.is_need_filter(
title, listpage_url, True)
# print(level_score, score_detail)
logger.info(str(level_score) + '=' + score_detail)
# 入库分值，新闻要大于等于20，论坛要大于等于10
valid_score = 20
media_type = common.get_media_type(listpage_url)
if media_type == 'forum':
valid_score = 10
if level_score >= valid_score:
column = f"('{title}', '{listpage_url}', '{record_md5_id}', '{website_no}', {column_extraction_deep}, '{domain_code}', '{host_code}', '{level_score}', '{score_detail}')"
column_list.append(column)
# 批量插入
values = ",".join(column_list)
insert_column = f"insert ignore into column_link(Title, URL, record_md5_id, website_no, column_extraction_deep, domain_code, host_code, level_score, score_detail) values{values};"
# print(insert_column)
common.query_mysql(database_config, insert_column)
return True
except Exception as e:
logger.error(traceback.format_exc())
async def get_response(semaphore,
url,
column_extraction_deep=1,
domain_code_source=None,
website_no=None):
try:
async with semaphore:
timeout = aiohttp.ClientTimeout(total=20)
# ValueError: too many file descriptoersin select()报错问题
# 一般是并发请求数太大导致的，通常通过减少并发数解决。
#
# 我遇到的情况：并发量设置的不高，运行一段时间后报该错误。通过搜索、调试，最后看aiohttp文档时发现是因为请求的https站点的服务器没有正确完成ssl连接，需要指定一个叫enable_cleanup_closed的参数为True：
#
# session = aiohttp.ClientSession(connector=aiohttp.TCPConnector(enable_cleanup_closed=True)
# 官方对enable_cleanup_closed参数的解释：
#
# Some ssl servers do not properly complete SSL shutdown process, in that case asyncio leaks SSL connections.
# If this parameter is set to True, aiohttp additionally aborts underlining transport after 2 seconds. It is off by default.
#
# 作者：Ovie
# 链接：https://www.jianshu.com/p/f7af4466f346
# 来源：简书
# 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
connector = aiohttp.TCPConnector(
limit=60, verify_ssl=False,
enable_cleanup_closed=True) # 60小于64。也可以改成其他数
async with aiohttp.ClientSession(timeout=timeout,
connector=connector) as session:
headers = {
"user-agent":
"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.113 Safari/537.36",
}
async with session.get(url, headers=headers) as response:
text = await response.text()
root = etree.HTML(
text, parser=etree.HTMLParser(encoding='utf-8'))
# print(url)
logging.info(url)
column_extraction_deep = int(column_extraction_deep) + 1
items = root.xpath('//a')
column_list = []
# print(len(items))
for item in items:
title = "".join(item.xpath('.//text()'))
listpage_url = "".join(item.xpath('./@href'))
listpage_url = urljoin(url, listpage_url)
# 去掉标点符号
title = common.filter_punctuation(title)
listpage_url = common.match_url(listpage_url)
# 计算url MD5 先去掉http和末尾斜杆
md5_source = listpage_url
md5_source = md5_source.replace('http://', '')
md5_source = md5_source.replace('https://', '')
md5_source = md5_source.rstrip('/')
record_md5_id = common.get_token(md5_source)
domain_code = common.get_domain_code(listpage_url)
host_code = common.get_host_code(listpage_url)
# domain 要与源域名一致
if domain_code_source != domain_code:
continue
# 垃圾词、垃圾域名过滤
level_score, score_detail = common.is_need_filter(
title, listpage_url, False)
# print(level_score, score_detail)
logging.info(str(level_score) + '=' + score_detail)
if level_score > 20:
column = f"('{title}', '{listpage_url}', '{record_md5_id}', '{website_no}', {column_extraction_deep}, '{domain_code}', '{host_code}', '{level_score}', '{score_detail}')"
column_list.append(column)
# 批量插入
values = ",".join(column_list)
insert_column = f"insert ignore into column_link(Title, URL, record_md5_id, website_no, column_extraction_deep, domain_code, host_code, level_score, score_detail) values{values};"
# print(insert_column)
common.query_mysql(extractor_118, insert_column)
return True
except Exception as e:
if len(str(e)) > 0:
logging.error(str(e))
return False
async def create_task():
semaphore = asyncio.Semaphore(100) # 限制并发量为500
try:
# 开始大循环，每次调度100个url，异步运行全部完成之后，才再添加
for j in range(1):
logging.critical("j:" + str(j) + "----------------------------")
start_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
begin = time.time()
tasks = []
answers = []
# 查询待采集目标
select_column = f"select Column_Link_ID, Column_Extraction_Deep, URL, Domain_Code, Website_No, Extracted_flag " \
f"from column_link where Extracted_flag is null " \
f"ORDER BY Column_Extraction_Deep limit 100;"
# print(select_column)
results = common.query_mysql(extractor_118, select_column)
# 更新Extracted_flag
id_list = [0]
for result in results:
id_list.append(result["Column_Link_ID"])
id_list = tuple(id_list)
update_flag = f"update column_link set Extracted_flag='S' where Column_Link_ID in {id_list};"
# print(update_flag)
common.query_mysql(extractor_118, update_flag)
# 开始任务
for result in results:
# print(result)
url = result["URL"]
domain_code = result["Domain_Code"]
website_no = result["Website_No"]
column_extraction_deep = result["Column_Extraction_Deep"]
if not column_extraction_deep:
column_extraction_deep = 0
# 最多采集3层
if column_extraction_deep <= 3:
task = asyncio.ensure_future(
get_response(semaphore, url, column_extraction_deep,
domain_code, website_no))
tasks.append(task)
# 先等待上面的任务完成
start_tasks = asyncio.all_tasks()
logging.critical("start_tasks: " + str(len(start_tasks)))
if len(start_tasks) > 510:
for t in tasks:
t.cancel()
try:
for next_to_complete in asyncio.as_completed(tasks,
timeout=100):
answer = await next_to_complete
if answer:
answers.append(answer)
# print(answer)
except asyncio.TimeoutError as e:
logging.critical("error_tasks: " +
str(len(asyncio.all_tasks())))
for t in tasks:
t.cancel()
logging.critical("end_tasks: " + str(len(asyncio.all_tasks())))
# result = await asyncio.gather(*tasks)
# print(result)
logging.critical("start_time: " + start_time)
logging.critical("tasks:" + str(len(tasks)))
logging.critical("answers:" + str(len(answers)))
end_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
logging.critical("end_time: " + end_time)
finish = time.time()
duration = int(finish) - int(begin)
logging.critical("duration: " + str(duration) + "s")
except Exception as e:
if len(str(e)) > 0:
logging.error(str(e))
column_list = []
for item in results:
author_name = item['_source']['title']
author_url = item['_source']['listpage_url']
author_id = author_url.split('&uid=')[1]
author_type = 'no_data'
is_added = 0
website = 'beijingshijianhao'
column = f"('{author_name}', '{author_url}', '{author_id}', '{author_type}', {is_added}, '{website}')"
column_list.append(column)
values = ",".join(column_list)
insert_column = f"insert ignore into author_other(author_name, author_url, author_id, author_type, is_added, website) values{values};"
print(insert_column)
common.query_mysql(extractor_118, insert_column)
for i in range(0, int(total / 100) + 1):
column_list = []
# scroll参数必须指定否则会报错
query_scroll = es.scroll(scroll_id=scroll_id, scroll='5m')['hits']['hits']
for item in query_scroll:
author_name = item['_source']['title']
author_url = item['_source']['listpage_url']
author_id = author_url.split('&uid=')[1]
author_type = 'no_data'
is_added = 0
website = 'beijingshijianhao'
column = f"('{author_name}', '{author_url}', '{author_id}', '{author_type}', {is_added}, '{website}')"
column_list.append(column)
def main(start, end):
extractor_116 = {
'host': '192.168.1.116',
'port': 3306,
'user': '******',
'passwd': 'poms@db',
'db': 'mymonitor'
}
extractor_118 = {
'host': '192.168.1.118',
'port': 3306,
'user': '******',
'passwd': 'poms@db',
'db': 'datasource'
}
select_column = f'''
SELECT
lu.listpage_url,
lu.listpage_title,
w.Website_Important_Level
FROM
listpage_url lu
LEFT JOIN website w ON lu.website_no = w.website_no
WHERE
w.Website_Name like'%新浪微博%'
and w.Website_Name like'%作者列表%'
and lu.ListPage_URL_ID BETWEEN {start} AND {end};
'''
try:
results = common.query_mysql(extractor_116, select_column)
column_list = []
count = 0
print(len(results))
for item in results:
author_name = item['listpage_title']
# author_account = item['listpage_url'].replace('http://', '')
author_url = item['listpage_url']
try:
author_id = author_url.split('&id=100505')[1].split(
'&feed_type')[0]
author_type = 'user'
except Exception as e:
try:
author_id = author_url.split('weibo.com/u/')[1].replace(
'?is_all=1', '')
author_type = 'user'
except Exception as e:
try:
author_id = author_url.split(
'weibo.com/p/')[1].replace('/wenzhang', '')
author_type = 'page'
except Exception as e:
author_id = author_name
author_type = ''
is_added = 1
column = f"('{author_name}', '{author_url}', '{author_id}', '{author_type}', {is_added})"
column_list.append(column)
# 每1万条插入一次，分批
if count > 0 and not count % 10000:
values = ",".join(column_list)
insert_column = f"insert ignore into author_weibo(author_name, author_url, author_id, author_type, is_added) values{values};"
# print(insert_column)
common.query_mysql(extractor_118, insert_column)
column_list = []
print(count)
count += 1
values = ",".join(column_list)
insert_column = f"insert ignore into author_weibo(author_name, author_url, author_id, author_type, is_added) values{values};"
# print(insert_column)
common.query_mysql(extractor_118, insert_column)
except Exception as e:
print(e)
def main():
fn = open('listpage.txt', 'r', encoding='UTF-8') # 打开文件
column_list = []
for i in fn:
try:
title = i.split('=')[0].strip()
listpage_url = i.split('=')[1].strip()
domain_code = common.get_domain_code(listpage_url)
host_code = common.get_host_code(listpage_url)
host_code_index = listpage_url.index(host_code)+len(host_code)
listpage_url = listpage_url[0:host_code_index] + '/'
# 计算url MD5 先去掉http和末尾斜杆
md5_source = listpage_url
md5_source = md5_source.replace('http://', '')
md5_source = md5_source.replace('https://', '')
md5_source = md5_source.rstrip('/')
record_md5_id = common.get_token(md5_source)
if len(title) < 1:
try:
response = requests.get(listpage_url, timeout=5)
# print(response.status_code)
if response.status_code == 200:
encoding = get_encoding(response)
# print(encoding)
response.encoding = encoding
soup = BeautifulSoup(response.text, 'lxml')
# print(soup.title.text)
title = soup.title.text
else:
continue
except:
pass
try:
title = title.split('－')[0].strip()
except Exception as e:
title = title
try:
title = title.split('_')[0].strip()
except Exception as e:
title = title
try:
title = title.split('-')[0].strip()
except Exception as e:
title = title
level_score, Score_Detail = common.is_need_filter(title, listpage_url, True)
# print(level_score, Score_Detail, title, listpage_url)
if level_score > -100:
level_score = '100'
Score_Detail = '"{\"status\": True, \"message\": \"root page\"}"'
website_no = 'AD_SELECTED'
column_extraction_deep = 0
column = f"('{title}', '{listpage_url}', '{record_md5_id}', '{website_no}', {column_extraction_deep}, '{domain_code}', '{host_code}', '{level_score}', '{Score_Detail}')"
# column_list.append(column)
print(column)
# 批量插入
extractor_118 = {'host': '192.168.1.118', 'port': 3306, 'user': '******', 'passwd': 'poms@db',
'db': 'mymonitor'}
values = column
insert_column = f"insert ignore into column_link(Title, URL, record_md5_id, website_no, column_extraction_deep, domain_code, host_code, level_score, Score_Detail) values{values};"
# print(insert_column)
try:
common.query_mysql(extractor_118, insert_column)
except Exception as e:
pass
except Exception as e:
pass
fn.close() # 关闭文件
