def main(self, req): #조회하는 쿼리 id = req['action']["clientExtra"]["id"] user_token = req['userRequest']['user']['properties'][ 'plusfriendUserKey'] print(user_token + '유저 토큰') query = "select * from festival_tb where id = " + str(id) db_obj = DBconncter().select_query(query) #사용자가 조회 한 것을 넣는 쿼리 user_token = req['userRequest']['user']['properties'][ 'plusfriendUserKey'] DBconncter().insert_festa_desc_query(user_token, id) return ui.festa_description(db_obj[0])
def option_classification(self): if self.sentence == "나가기": user_token = self.requset_obj['userRequest']['user']['properties'][ 'plusfriendUserKey'] DBconncter().selected_out(user_token) return ui.text_message("완료되었습니다. 또 다른 축제를 검색해보세요. 😃") self.pred, self.label = rnn_predict(self.sentence) if Optionclassification.unnecessary_option( self) == -1 and self.label != "주소": #필요 없는 말을 했을 경우 return ui.text_message("축제에 대한 정보를 묻는게 아닌거 같은데 다시 말해줘") elif self.label == "주소": return Option(self.requset_obj).get_addr() elif self.label == "주차": return Option(self.requset_obj).get_parkinglot() elif self.label == "날씨": return Option(self.requset_obj).get_weather() elif self.label == "맛집": return Option(self.requset_obj).get_restaurant() elif self.label == "카페": return Option(self.requset_obj).get_cafe() elif self.label == "연관": return RelationOption(self.requset_obj).get_list() elif self.label == "인기": return Option(self.requset_obj).get_popular_festa() else: print("[SERVER] 재입력바랍니다")
def picture_find(utterance): ui_context = None result = detect_labels_uri(utterance) if result == 0: return ui.text_message("사진에 맞는 축제 못찾겠어") else: pic_label_list = result pic_label_list = pic_label_list.split('!') for a in pic_label_list: print(a) id_list = [] title = "" for pic_obj in pic_label_list: id_list += word_pupose(pic_obj) title += pic_obj + ',' if (len(id_list) != 0): query = 'select * from (select * from festival_tb where enddate > sysdate()) A where ' # 기본 쿼리 for id in id_list: query += "id = " + str(id) + " or " print(query) db_obj = DBconncter().select_query(query[0:len(query) - 3]) if len(db_obj) == 0: ui_context = ui.text_message("사진에 맞는 축제 못찾겠어") else: ui_context = ui.festa_list_ui(db_obj[0:5], db_obj[5:], title[0:len(title) - 1]) else: ui_context = ui.text_message("사진에 맞는 축제 못찾겠어") return ui_context
def now_festa_list(): list = [['축제']] count_query = 'select COUNT(*) from (select * from festival_tb where enddate > sysdate()) A' row_count = (DBconncter().select_query(count_query))[0][0] i = 0 while i < row_count: query = 'select title from (select * from festival_tb where enddate > sysdate()) A limit ' + str( i) + ', 4' # 기본 쿼리 db_obj = DBconncter().select_query(query) row_list = [re.sub('[0-9]+', '', obj[0]) for obj in db_obj] print(row_list) if len(row_list) == 4: list.append(row_list) i += 4 return list
def get_parkinglot(self): #주차장 조회 query = 'select region, title, address, getX, getY, img from festival_tb where id like ' + str( Option.get_fest_id(self)) + ';' data = DBconncter().select_query(query) datalist = list( data[0] ) #datalist[0] == region datalist[1] == title ... datalist[4] == getY return ui.parkinglot_ui(datalist)
def get_list(self): sim_list = RelationOption.most_similar(self) print(sim_list) list = [ obj['id'] for obj in sim_list if obj['score'] > 0.5] #스코어가 0.5 이상일때 query = "select * from (select * from festival_tb where enddate > sysdate()) A where " for id in list[:3]: #최대 3개만 보여 query += "id = "+str(id)+" or " query = query[:len(query)-3] db_obj = DBconncter().select_query(query) #연관된 축제 불러와 쿼리 가져 옴 title = RelationOption.festa_title(self) #선택했던 축제의 이름 return ui.festa_list_ui(db_obj, [], title) #ui 호출
def get_popular_festa(self): query = 'select * from popular_festa order by save_date desc limit 1' #크롤링된 인기축제 호출 data = DBconncter().select_query(query) crawled_list = data[0][1:] #쓸모없는 첫번째 칼럼 삭제 print('%%%%%%%%%%%%%%%%%%%%%%%%crawled_list%%%%%%%%%%%%%%%%%%%%%%%%\n', crawled_list) query = 'select id, title, content, thumbnail, link from festival_tb where title like "%' + str( crawled_list[0]) #인기축제 쿼리문 for i in range(1, len(crawled_list)): query = query + '%" or title like "%' + str(crawled_list[i]) query = query + '%"' data_list = DBconncter().select_query(query) #각 가져오기 result_list = [] for title in crawled_list: #순서가 바뀐 datalist를 재 sorting하여 result_list에 저장 for v in data_list: if title == v[1]: result_list.append(v) return ui.popular_festa_ui(result_list)
def get_weather(self): query = 'select region, title, address, startdate, enddate, getX, getY from festival_tb where id like ' + str( Option.get_fest_id(self)) + ';' data = DBconncter().select_query(query) festlist = list(data[0]) query = 'select * from weather_tb where region like "' + str( festlist[0]) + '";' data = DBconncter().select_query(query) weatherDBlist = list(data) feststartdate, festenddate = festlist[3], festlist[4] #혹시 몰라 끝나는날까지 추출 placeXY = {'x': festlist[5], 'y': festlist[6]} start_date = datetime.strptime(feststartdate, "%Y.%m.%d") end_date = datetime.strptime(festenddate, "%Y.%m.%d") print("Transformed date : start - ", start_date, "end - ", end_date) current_date = (datetime.now() + timedelta(hours=9)).replace( hour=0, minute=0, second=0, microsecond=0) #오늘 날짜 #서버시간은 미국기준이라 9시간더함 print("시작날짜", start_date, "- 금일 날짜", current_date, '=', (start_date - current_date).days) if (start_date - current_date ).days > 7: #축제 시작일이 금일을 기준으로 openWeather 최대 예보일(금일이후 7일)을 넘어갈 때 return ui.month_weather_ui(start_date.month, weatherDBlist) else: weekly_weather = get_weekly_weather(placeXY) fest_idx_list = getIndexList(start_date, end_date, current_date) print("final index = " + str(fest_idx_list)) return ui.each_weather(weekly_weather, fest_idx_list)
def get_addr(self): #축제 주소 조회 lo_search = Location_search_kakaomap_api() if lo_search.check(self.sentence) == 0: query = 'select region, title, address, getX, getY, img from festival_tb where id like ' + str( Option.get_fest_id(self)) + ';' data = DBconncter().select_query(query) datalist = list( data[0] ) #datalist[0] == region datalist[1] == title ... datalist[4] == getY return ui.address_ui(datalist) else: title, place_list = lo_search.searchAddr(self.usertoken) return ui.keyword_place_ui(place_list, title)
def get_cafe(self): cafe_list = [] query = 'select getX, getY from festival_tb where id like ' + str( Option.get_fest_id(self)) + ';' data = DBconncter().select_query(query) datalist = list(data[0]) cafe_list = get_cafe_list(datalist) print("[SERVER] Get %d item(s)" % len(cafe_list)) for d in cafe_list: print(d) if len(cafe_list) < 1: #근방에 카페가 있을 때, 없을 때 return ui.empty_items_ui('c') else: return ui.cafe_ui(datalist, cafe_list)
def get_restaurant(self): restaurant_list = [] query = 'select getX, getY from festival_tb where id like ' + str( Option.get_fest_id(self)) + ';' data = DBconncter().select_query(query) datalist = list(data[0]) restaurant_list = get_restaurant_list(datalist) print("[SERVER] Get %d item(s)" % len(restaurant_list)) for d in restaurant_list: print(d) if len(restaurant_list) < 1: #근방에 맛집이 없을 때, 있을 때 return ui.empty_items_ui('r') else: return ui.restaurant_ui(datalist, restaurant_list)
def word2vec_checker(self, pupose_words, title): sim_obj_list = [] for word in pupose_words: print(word) for sim_word in word2vec_obj.most_similar(word, 5): #연관된 단어 5개 추출 print(sim_word) id_query = '' sim_word_list = self.word_pupose( sim_word) #해당 단어를 가진 축제 있는지 체크 if len(sim_word_list) != 0: for id in sim_word_list: id_query += "id = " + str(id) + " or " if id_query != '': exe_temp_query = self.temp_query + " (" + id_query[ 0:len(id_query) - 3] + ")" db_obj = DBconncter().select_query(exe_temp_query) print(len(db_obj)) if len(db_obj) != 0: obj = {'word': sim_word, 'festa_list': db_obj} sim_obj_list.append(obj) if len(sim_obj_list) == 0: return ui.text_message(title + "에 맞는 열릴 축제가 없나봐 ㅠ.ㅠ") else: return ui.word2vec_recommed_ui(pupose_words, sim_obj_list)
def get_festa_id(self): query = "select festa_id from user_tb where user_token = '"+self.user_token+"'" self.id = DBconncter().select_query(query)[0][0]
def get_fest_id(self): #유저토큰을 비교하여 축제 ID를 받아오는 메소드 festaid_query = 'select festa_id from user_tb where user_token like "' + str( self.usertoken) + '";' return DBconncter().select_query(festaid_query)[0][0]
sorted_df = sorted(df_dict.items(), key=lambda t: t[1], reverse=True) #dict의 key, value를 value 기준으로 내림차순 sorting # print(json.dump(sorted_df, ensure_ascii=False, indent='\t')) sorted_df = sorted_df[:40] #리스트 40개로 제한 list [[축제명,스코어],[축제명,스코어]...[축제명,스코어]] for i in range(40): #스코어 삭제 sorted_df[i] = sorted_df[i][0] #정렬된 축제명만을 리스트로 삽입 query = 'select title from festival_tb where title like "%' + str( sorted_df[0]) #인기축제40개 쿼리문 for i in range(1, 40): # query = query + '%" or title like "%' + str(sorted_df[i]) # query = query + '%"' # data = DBconncter().select_query(query) datalist = list(data) result_list = [] for title in sorted_df: #순서가 바뀐 datalist를 재 sorting하여 result_list에 저장 for db_title in datalist: if title == re.sub('[0-9]+', '', db_title[0]): result_list.append(db_title) for i in range(40): result_list[i] = result_list[i][0] print(result_list) currentDate = datetime.now().strftime("%Y.%m.%d") #추가 일자 str_fest = ''
sys.exit(0) host = 'mydb.cstof8mab94c.ap-northeast-2.rds.amazonaws.com' user = '******' password = '******' db = 'festabot' import sys import os sys.path.insert( 0, os.path.abspath(os.path.join(os.path.dirname(__file__), '../../'))) from common.DBconncter import DBconncter query = 'select id, img from (select * from festival_tb where enddate > sysdate()) A' db_obj = DBconncter().select_query(query) # 조건이 있으면 db에 넣음 for a in db_obj: time.sleep(5) conn = pymysql.connect(host=host, user=user, password=password, db=db, charset='utf8') curs = conn.cursor() thumbnail = make_thumbnail(a[1])['thumbnail_image_url'] print(a[0]) print( '------------------------------------------------------------------------' ) sql = "UPDATE festival_tb SET thumbnail = %s WHERE id = %s"
query = re.sub("[0-9]", '', title) # 검색을 원하는 문자열로서 UTF-8로 인코딩한다. 제목의 숫자는 없앰 display = 10 # 검색 결과 출력 건수 지정, 10(기본값),100(최대) start = 1 # 검색 시작 위치로 최대 1000까지 가능 sort = "sim" # 정렬 옵션: sim(유사도순, 기본값), date(날짜순) blog_count = Naver_clawing_for_keyword.get_blog_count( self, query, display) for start_index in range(start, blog_count + 1, display): Naver_clawing_for_keyword.get_blog_post(self, query, display, start_index, sort) return self.all_blog_post_text query = 'select id, title from (select * from festival_tb where enddate > sysdate()) A' db_obj = DBconncter().select_query(query) okt = Okt() def okt_tokenizer(sent): words = okt.pos(sent, join=True) words = [ w for w in words if ('Noun' in w or 'Determiner' in w or 'Verb' in w or 'Adjective' in w ) ] return words summarizer = KeysentenceSummarizer(tokenize=okt_tokenizer,
def selected_festa_checker(self): #사용자가 축제를 선택한 지 보여주는 함수 checker = DBconncter().select_query('select * from user_tb where =' + self.user) is () return checker
def func_list(self): date_query = "" purpose_query = "" region_list = "" title = "" query_cheker = False token_idx = 0 pupose_words = [] query = 'select * from (select * from festival_tb where enddate > sysdate()) A where ' # 기본 쿼리 token = FindPurpose.okt_tokenizer(self.sentence) if len(token) != 0: # 토큰화하여 처리할 문장이 있다 while token_idx < len(token): word, tag = token[token_idx][0], token[token_idx][1] if tag == 'Number': title += word + ',' # 보여질 제목에 들어갈 문장 date_query += FindPurpose.tag_number(self, word) # 조건 한줄 씩 추가 if tag == 'Determiner' or tag == 'Modifier': if DateChecker.de_month_check(word): # 월 인지 체크 title += word + '달 뒤,' date_query += FindPurpose.tag_Determiner( self, word) # 조건 한줄 씩 추가 if tag == 'Noun': if DateChecker.month_check(word): # 월 인지 체크 title += word + ',' word = DateChecker.month_generater(word) if len(word) == 1: word = '0' + word # 한자릿 수 일 경우 앞에 0붙임 두자릿수면 그냥 한다 ex) 11, 12 mon_qu = "startdate between '" + year + "." + word + ".01' and '" + year + "." + word + ".31' or " print(mon_qu) date_query += mon_qu # 조건 한줄 씩 추가 elif word in [ '이번', '다음주', '다음', '다다', '주말', '월요일', '화요일', '수요일', '목요일', '금요일', '토요일', '일요일', '월', '화', '수', '목', '금', '토', '일' ]: #향후 있을 날짜 처리 ex) 이번 달, 다음 주 일요일 #이번, 다음, 다다음, 토큰화 방식이 너무 다름 if word == '이번': if token[token_idx + 1][0] == '주': #주말을 물어 봤을 경우 if token[token_idx + 2][0] in [ '월요일', '화요일', '수요일', '목요일', '금요일', '토요일', '일요일', '월', '화', '수', '목', '금', '토', '일' ]: day_of_the_week = 0 for a in [ ('월요일', 0), ('화요일', 1), ('수요일', 2), ('목요일', 3), ('금요일', 4), ('토요일', 5), ('일요일', 6), ('월', 0), ('화', 1), ('수', 2), ('목', 3), ('금', 4), ('토', 5), ('일', 6), ]: if token[token_idx + 2][0] == a[0]: #무슨 요일인지 찾음 day_of_the_week = a[1] today = datetime.today() day = today + timedelta( days=-today.weekday() + day_of_the_week, weeks=0) # 월요일 mon_qu = "(startdate < '" + day.strftime( "%Y.%m.%d" ) + "' and enddate > '" + day.strftime( "%Y.%m.%d") + "') or" title += word + token[token_idx + 1][0] + ',' + token[ token_idx + 2][0] + ',' del token[token_idx + 1] # 이번, "주" 제거 del token[token_idx + 1] # 이번, "주", '요일' 제거 date_query += mon_qu # 조건 한줄 씩 추가 else: #주간으로 물어 봤을 경우 today = datetime.today() mon_day = today + timedelta( days=-today.weekday(), weeks=0) #월요일 sun_day = today + timedelta( days=-today.weekday() + 6, weeks=0) #일요일 mon_qu = "startdate between '" +mon_day.strftime("%Y.%m.%d")+\ "' and '"+sun_day.strftime("%Y.%m.%d")+"' or " title += word + token[token_idx + 1][0] + ',' del token[token_idx + 1] #이번, "주" 제거 date_query += mon_qu # 조건 한줄 씩 추가 elif token[token_idx + 1][0] == '달' or token[ token_idx + 1][0] == '월': #달, 월 로 쳤을 경우 st_month = ( datetime(int(year), int(month), 1) + relativedelta(months=0) ).strftime("%Y.%m.%d") ed_month = ( datetime(int(year), int(month), 30) + relativedelta(months=0) ).strftime("%Y.%m.%d") title += word + token[token_idx + 1][0] + ',' del token[token_idx + 1] # 이번, "달" 제거 mon_qu = "startdate between '" + st_month + "' and '" + ed_month + "' or " date_query += mon_qu # 조건 한줄 씩 추가 elif word == '다음주': try: if token[token_idx + 1][0] in [ '월요일', '화요일', '수요일', '목요일', '금요일', '토요일', '일요일', '월', '화', '수', '목', '금', '토', '일' ]: day_of_the_week = 0 for a in [ ('월요일', 0), ('화요일', 1), ('수요일', 2), ('목요일', 3), ('금요일', 4), ('토요일', 5), ('일요일', 6), ('월', 0), ('화', 1), ('수', 2), ('목', 3), ('금', 4), ('토', 5), ('일', 6), ]: if token[token_idx + 1][0] == a[0]: #무슨 요일인지 찾음 day_of_the_week = a[1] today = datetime.today() day = today + timedelta( days=-today.weekday() + day_of_the_week, weeks=1) # 월요일 mon_qu = "(startdate < '" + day.strftime( "%Y.%m.%d" ) + "' and enddate > '" + day.strftime( "%Y.%m.%d") + "') or" title += word + token[token_idx + 1][0] + ',' del token[token_idx + 1] # 다음주, "요일" 제거 date_query += mon_qu # 조건 한줄 씩 추가 else: today = datetime.today() mon_day = today + timedelta( days=-today.weekday(), weeks=1) # 월요일 sun_day = today + timedelta( days=-today.weekday() + 6, weeks=1) # 일요일 mon_qu = "startdate between '" + mon_day.strftime("%Y.%m.%d") + \ "' and '" + sun_day.strftime("%Y.%m.%d") + "' or " title += word + ',' date_query += mon_qu # 조건 한줄 씩 추가 except IndexError: today = datetime.today() mon_day = today + timedelta( days=-today.weekday(), weeks=1) # 월요일 sun_day = today + timedelta( days=-today.weekday() + 6, weeks=1) # 일요일 mon_qu = "startdate between '" + mon_day.strftime("%Y.%m.%d") + \ "' and '" + sun_day.strftime("%Y.%m.%d") + "' or " title += word + ',' date_query += mon_qu # 조건 한줄 씩 추가 elif word == '다음': if token[token_idx + 1][0] == '달': #주말을 물어 봤을 경우 st_month = ( datetime(int(year), int(month), 1) + relativedelta(months=1) ).strftime("%Y.%m.%d") ed_month = ( datetime(int(year), int(month), 30) + relativedelta(months=1) ).strftime("%Y.%m.%d") title += word + token[token_idx + 1][0] + ',' del token[token_idx + 1] # 다음, "" 제거달 mon_qu = "(startdate < '" + st_month + "' and enddate > '" + ed_month + "') or" date_query += mon_qu # 조건 한줄 씩 추가 elif word == '다다': if token[token_idx + 1][0] == '음주': #다다음주 물어 봤을 경우 today = datetime.today() mon_day = today + timedelta( days=-today.weekday(), weeks=2) # 월요일 sun_day = today + timedelta( days=-today.weekday() + 6, weeks=2) # 일요일 mon_qu = "startdate between '" + mon_day.strftime("%Y.%m.%d") + \ "' and '" + sun_day.strftime("%Y.%m.%d") + "' or " title += word + token[token_idx + 1][0] + ',' date_query += mon_qu # 조건 한줄 씩 추가 if token[token_idx + 1][0] == '음달': #다다음주 물어 봤을 경우 st_month = ( datetime(int(year), int(month), 1) + relativedelta(months=2) ).strftime("%Y.%m.%d") ed_month = ( datetime(int(year), int(month), 30) + relativedelta(months=2) ).strftime("%Y.%m.%d") title += word + token[token_idx + 1][0] + ',' del token[token_idx + 1] # 다음, "" 제거달 mon_qu = "(startdate < '" + st_month + "' and enddate > '" + ed_month + "') or" date_query += mon_qu # 조건 한줄 씩 추가 if word in [ '월요일', '화요일', '수요일', '목요일', '금요일', '토요일', '일요일', '월', '화', '수', '목', '금', '토', '일' ]: day_of_the_week = 0 for a in [ ('월요일', 0), ('화요일', 1), ('수요일', 2), ('목요일', 3), ('금요일', 4), ('토요일', 5), ('일요일', 6), ('월', 0), ('화', 1), ('수', 2), ('목', 3), ('금', 4), ('토', 5), ('일', 6), ]: if word == a[0]: # 무슨 요일인지 찾음 day_of_the_week = a[1] today = datetime.today() day = today + timedelta( days=-today.weekday() + day_of_the_week, weeks=0) # 월요일 mon_qu = "(startdate < '" + day.strftime( "%Y.%m.%d" ) + "' and enddate > '" + day.strftime( "%Y.%m.%d") + "') or" title += word + ',' date_query += mon_qu # 조건 한줄 씩 추가 elif region_check_flg(word): # 지역인지 체크 오타 체크 부분 region = region_return(word) title += region + ',' region_list += "'" + region + "'," else: title += word + ',' pupose_words.append(word) #목적이 있는 단어를 하나씩 추가 id_list = FindPurpose.word_pupose(self, word) #목적 단어 pk 추가 if len(id_list) != 0: for festa_id in id_list: purpose_query += "id = " + str( festa_id) + " or " query_cheker = True token_idx += 1 #다음 인덱스 if date_query != "": query += "(" + date_query[ 0:len(date_query) - 3] + ") and" # where절에 마지막 or를 날린다 #날짜를 쿼리에 넣음 self.temp_query += "(" + date_query[0:len(date_query) - 3] + ") and" #임시 쿼 query_cheker = True if region_list != "": query += " region in (" + region_list[:len( region_list ) - 1] + ") and" # 조건 한줄 추가 #where region in ('부산','서울') self.temp_query += " region in (" + region_list[:len( region_list) - 1] + ") and" #임시 쿼리 query_cheker = True if purpose_query != "": query += "(" + purpose_query[ 0:len(purpose_query) - 3] + ") and" # where절에 마지막 or를 날린다 #날짜를 쿼리에 넣음 query_cheker = True if query_cheker: # 조건이 들어가는 쿼리가 있음 query = query[0:len(query) - 3] print(query) db_obj = DBconncter().select_query(query) # 조건이 있으면 db에 넣음 if len(db_obj) == 0: # word2vec으로 축제들을 가져왔지만 축제가 있는지 없는지 return self.word2vec_checker(pupose_words, title) else: return ui.festa_list_ui(db_obj[0:5], db_obj[5:], title[0:len(title) - 1]) else: return self.word2vec_checker(pupose_words, title) elif len(token) == 0: # 사용자의 말에 축제 조건이 없음 return ui.text_message("어떤 축제를 원하는지 다시 말해줄래?\n" "(ex : 지역, 월, 목적)")
def festa_title(self): query = "select title from festival_tb where id = '" + str(self.id) + "'" return DBconncter().select_query(query)[0][0]