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]
