# -*- coding: utf-8 -*- from datetime import datetime from functions.mode2.getSocialData import ( getNews, getSomeRelatedWords, getOvertimes ) import sys sys.path.append("/var/local/mode2") from functions.webanalysis_action import mwDB def saveNews(search_keywords, search_keyword_id, domain=False): """ @param list setting_keywords @param int search_keyword_id """ print('saveNews() in') Db = mwDB(domain) try: news_dict = {} for keyword in search_keywords: print('saveNews() keyword for search;') print(keyword) # {'news':[{'url':'url1', 'title':'title1'},,,{'url':'urlN', 'title':'titleN'}] news_dict[keyword] = getNews(keyword) exist_news_keys = [ keyword for keyword in search_keywords if news_dict[keyword]['news']] if len(exist_news_keys) >= 1: query = 'SELECT MAX(search_id) FROM social_news' res = Db.fetchone(query) if res[0] == None: search_id = 0 else: search_id = int(res[0]) + 1 with Db.connection.cursor() as cursor: for search_keyword in exist_news_keys: pages = news_dict[search_keyword]['news'] for page in pages: try: news_url = page['url'] news_title = page['title'] register_date = datetime.now().strftime( '%Y-%m-%d %H:%M:%S') # current timestamp insert_data = [ search_id, search_keyword_id, search_keyword, news_title, news_url, register_date] insert_string = ', '.join( ['%s'] * len(insert_data)) query = "INSERT INTO social_news (search_id, search_keyword_id, search_keyword, title, url, register_date) VALUES (%s);" % insert_string cursor.execute(query, insert_data) except Exception as e: print('saveNews() error') print(str(e), exc_info=True) print('saveNews() out') pass print('saveNews() out') Db.close() return True except Exception as e: print('saveNews() error') print(str(e), exc_info=True) print('saveNews() out') return False def saveRelatedWords(search_keywords, search_keyword_id, interest_term_related, domain=False): """ @param list setting_keywords @param int search_keyword_id @param str interest_term_related """ print('saveRelatedWords() in') try: Db = mwDB(domain) # {'all_keywords':data, 'A':data,,,'D':data} related_results = getSomeRelatedWords( search_keywords, interest_term_related) if len(search_keywords) == 1: related_results[search_keywords[0] ] = related_results['all_keywords'] del related_results['all_keywords'] exist_data_keys = [ keyword for keyword in search_keywords if related_results[keyword][0]['word']] if len(exist_data_keys) > 0: query = 'SELECT MAX(search_id) FROM social_related_words' ret = Db.fetchone(query) if ret[0] == None: search_id = 0 else: search_id = int(ret[0]) + 1 with Db.connection.cursor() as cursor: for keyword in exist_data_keys: if keyword == "all_keywords": search_keyword = search_keywords[0] else: search_keyword = keyword if related_results[keyword][0]['word']: for result in related_results[keyword]: register_date = datetime.now().strftime('%Y-%m-%d %H:%M:%S') related_word = result['word'] word_count = result['value'] insert_data = [search_id, search_keyword_id, search_keyword, related_word, word_count, interest_term_related, register_date] insert_string = ', '.join( ['%s'] * len(insert_data)) query = 'INSERT INTO social_related_words (search_id, search_keyword_id, search_keyword, related_word, count, interest_term, register_date) VALUES (%s)' % insert_string cursor.execute(query, insert_data) print('saveRelatedWords() out') Db.close() return True except Exception as e: print('saveRelatedWords() error') print(str(e), exc_info=True) print('saveRelatedWords() out') return False def saveOvertime(setting_keywords, search_keyword_id, interest_term_over, domain=False): """ @param list setting_keywords @param int search_keyword_id @param str interest_term_over """ print('saveOvertime() in') try: Db = mwDB(domain) is_data = None overtime_results = {} tmp_overtime_results = getOvertimes( setting_keywords, interest_term_over) if len(tmp_overtime_results) > 1: for k in tmp_overtime_results: if tmp_overtime_results[k]['data']: overtime_results[k] = tmp_overtime_results[k] elif len(tmp_overtime_results) == 1: # for only one keyword overtime_results[setting_keywords[0] ] = tmp_overtime_results['all_keywords'] if len(overtime_results) > 0: is_data = True if is_data: query = 'SELECT MAX(search_id) FROM social_trend_overtime' ret = Db.fetchone(query) if ret[0] == None: search_id = 0 else: search_id = int(ret[0]) + 1 query = 'SELECT MAX(search_group_id) FROM social_trend_overtime' ret = Db.fetchone(query) if ret[0] == None: search_group_id = 0 # 1回のトレンド検索を表す. else: search_group_id = int(ret[0]) + 1 with Db.connection.cursor() as cursor: for key in overtime_results: # overtime_resultsのキーのencodingは'utf-8' # 'all_keywords':{data:[{'date':0000-00-00, 'key1':00, 'key2':00, 'keyw':00},,,,{}], label:{}} if key == "all_keywords": for skey in setting_keywords: search_type = 'compare' # pytrendで取得するデータ(results)のキーはunicode # search_keyword = skey.decode('utf-8') search_keyword = skey # overtime_results = {'data':'', 'label':''} for result in overtime_results[key]['data']: # 2017-07-26 -> 2017-07-26 00:00:00 day = result['date'] count = result[search_keyword] register_date = datetime.now().strftime( '%Y-%m-%d %H:%M:%S') # current timestamp insert_data = [search_id, search_group_id, search_keyword_id, search_type, search_keyword, count, day, interest_term_over, register_date] insert_string = ', '.join( ['%s'] * len(insert_data)) query = 'INSERT INTO social_trend_overtime (search_id, search_group_id, search_keyword_id, search_type, keyword, count, day, interest_term, register_date) VALUES (%s)' % insert_string cursor.execute(query, insert_data) else: search_type = 'single' # pytrendで取得するデータ(results)のキーはunicode # search_keyword = key.decode('utf-8') search_keyword = key for result in overtime_results[key]['data']: day = result['date'] count = result[search_keyword] register_date = datetime.now().strftime('%Y-%m-%d %H:%M:%S') insert_data = [search_id, search_group_id, search_keyword_id, search_type, search_keyword, count, day, interest_term_over, register_date] insert_string = ', '.join( ['%s'] * len(insert_data)) query = 'INSERT INTO social_trend_overtime (search_id, search_group_id, search_keyword_id, search_type, keyword, count, day, interest_term, register_date) VALUES (%s)' % insert_string cursor.execute(query, insert_data) search_group_id += 1 print('saveOvertime() out') Db.close() return True except Exception as e: print('saveOvertime() error') print(str(e), exc_info=True) print('saveOvertime() out') return False