def movie_detail_info(user_movie_name):
curs = cnnt.mk_cursor()
sql=f'''
SELECT bmi.movie_code, bmi.movie_name_kor as '제목',dir.director_name_kor as '감독',dbmi.opendate as '개봉일',dbmi.flim_class as '관람가',dbmi.story as '개요'
FROM BaseMovieInfo as bmi
left join DetailedBaseMovieInfo as dbmi
on bmi.movie_code=dbmi.movie_code
left join DirectorOfMovie as dof
on dof.movie_code=bmi.movie_code
left join Director as dir
on dof.movie_director_code=dir.director_code
where movie_name_kor="{user_movie_name}"'''
a = curs.execute(sql)
sqlresult=curs.fetchone()
curs.close()
moviecode,title,director,opendate,viewer,story = sqlresult
sql2=f'''SELECT bmi.movie_name_kor as '제목',act.actor_name_kor as '출연자'
from BaseMovieInfo as bmi
inner join ActorsOfMovie as aom
on aom.movie_code=bmi.movie_code
inner join Actors as act
on aom.movie_actor_code=act.actor_code
where movie_name_kor="{user_movie_name}"'''
curs = cnnt.mk_cursor()
b = curs.execute(sql2)
sql2result=curs.fetchall()
curs.close()
actorlist = []
for i in range(len(sql2result)):
actorlist.append(sql2result[i][1])
finalactor = ", ".join(actorlist)
curs = cnnt.mk_cursor()
sql3=f'''
SELECT bmi.movie_name_kor as '제목', gn.genre as '장르'
from BaseMovieInfo as bmi
inner join GenreOfMovie as gom
on gom.movie_code=bmi.movie_code
inner join Genre as gn
on gn.genre=gom.movie_genre
where movie_name_kor="{user_movie_name}"'''
c = curs.execute(sql3)
sql3result=curs.fetchall()
curs.close()
genrelist = []
for i in range(len(sql3result)):
genrelist.append(sql3result[i][1])
finalgenre = ",".join(genrelist)
return moviecode,title,director,opendate,viewer,story,finalactor,finalgenre
def find_by_score():
curs = cnnt.mk_cursor()
sql = '''
SELECT ba.movie_name_kor,
CASE WHEN viewer_score = 0 THEN round(ntz_score,2)
ELSE round((ntz_score+viewer_score)/2,2)
END AS average_score, giza_score
FROM DBtoday.MovieScore as ms
INNER JOIN BaseMovieInfo as ba
ON ms.movie_code = ba.movie_code
INNER JOIN DetailedBaseMovieInfo as dm
ON dm.movie_code = ms.movie_code
WHERE date(dm.opendate) > date_add(date(now()), interval -1 month)
Order by average_score DESC
LIMIT 10'''
a = curs.execute(sql)
sqlresult = curs.fetchall()
curs.close()
stringlist = []
namelist = []
for number, i in enumerate(sqlresult):
name, mean_score, giza = i[0], i[1], i[2]
namelist.append(name)
string = f"{number+1}위 : {name} | 평균평점 : {mean_score} | 기자평점 : {giza} \n"
stringlist.append(string)
final_string = "".join(stringlist)
return namelist, final_string
def make_movie_list():
curs = cnnt.mk_cursor()
sql = """SELECT movie_name_kor FROM DBtoday.BaseMovieInfo"""
a = curs.execute(sql)
sqlresult = curs.fetchall()
curs.close()
temp_list = []
for i in sqlresult:
temp_list.append(i[0])
return temp_list
def make_last_msg(user_key):
curs = cnnt.mk_cursor()
try:
sql =f'''SELECT * FROM DBtoday.KakaoMessage as KM Where user_key = '{user_key}' order by KM.timestamp desc, KM.index desc limit 1;'''
a = curs.execute(sql)
sqlresult = curs.fetchone()
curs.close()
print(sqlresult[3])
return sqlresult[3]
except:
return None
def currently_or_future_showing_movie(parameter):
if parameter == "curr":
banghang = "<"
else:
banghang = ">"
curs =cnnt.mk_cursor()
sql=f'''
SELECT bmi.movie_code, bmi.movie_name_kor, dbmi.opendate, sc.ntz_score
FROM BaseMovieInfo as bmi
inner join DetailedBaseMovieInfo as dbmi
on bmi.movie_code = dbmi.movie_code
inner join MovieScore as sc
on bmi.movie_code = sc.movie_code
where
(dbmi.opendate {banghang} date(now())) in (date(dbmi.opendate) > date_add(date(now()), interval -1 month))
order by opendate desc limit 10;
'''
a = curs.execute(sql)
sqlresult = curs.fetchall()
curs.close()
contentslist = []
namelist = []
if banghang == "<":
for i in sqlresult:
name, opendate, score = i[1], i[2], i[3]
namelist.append(name)
contents = f"{name} | 개봉 {opendate} | 평점 {score} \n"
contentslist.append(contents)
final_contents = "".join(contentslist)
else:
for i in sqlresult:
name, opendate = i[1], i[2]
namelist.append(name)
contents = f"{name} | 개봉예정 {opendate} \n"
contentslist.append(contents)
final_contents = "".join(contentslist)
return namelist, final_contents
final_contents = "".join(contentslist)
else:
for i in sqlresult:
name, opendate = i[1], i[2]
namelist.append(name)
contents = f"{name} | 개봉예정 {opendate} \n"
contentslist.append(contents)
final_contents = "".join(contentslist)
return namelist, final_contents
user, password, host, port, DB = cnnt.aws_basic_info()
target = f'mysql+pymysql://{user}:{password}@{host}:{port}/{DB}?charset=utf8'
movie_list = make_movie_list()
cur = cnnt.mk_cursor()
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = target
db = SQLAlchemy(app)
default_button_list = ["관객수 그래프 보기", "현재상영작 보기", "개봉예정작 보기", "평점순 현재상영작"]
@app.route('/keyboard')
def Keyboard():
dataSend = {
"type" : "buttons",
"buttons" : default_button_list
}
return jsonify(dataSend)