def search_recommended_actors():
sqlQuery = "select distinct Genre.genreName as title from Genre"
res2 = select(sqlQuery)
result2 = [k[0] for k in res2["rows"]]
if request.method == 'POST':
if request.form.get("dropdown"):
title = request.form['dropdown']
result = getRecommendedActorsByGenre(title)
return render_template('Actors.html', res=result, genres=result2)
elif request.form.get(
"startDate1"
): # returns actors who participated in at least 3 movies during the time period
date = request.form['startDate1'].split(" - ")
result4 = getKnownActors(date)
return render_template('Actors.html',
resKnown=result4,
genres=result2)
elif request.form.get("startDate2"):
# actors who have at least 3 movies between those dates with a popularity above 100 and a revenue-to-budget ratio of at least 6.5
date = request.form['startDate2'].split(" - ")
result3 = getSyccesfulActors(date)
return render_template('Actors.html',
resSuc=result3,
genres=result2)
elif request.form.get('actorName'):
actor = request.form['actorName']
result = getMediaByActor(actor)
return render_template('Actors.html',
resNames=result,
genres=result2)
def getRecommendedActorsByGenre(title):
try:
sqlQuery = "select * from " \
"(select a.actorName as title, count(*) as cnt " \
"from Actors a, MoviesGenre mg, Movie m , ActorsMovie am , Genre g , " \
"(select Genre.genreId as id from Genre where genreName=%s) AS temp, " \
"(select g.genreId,g.genreName,avg(voteCount) as avgvotecount from Movie m , Genre g, MoviesGenre mg " \
"where m.apiId=mg.apiId and g.genreId=mg.genreId group by g.genreId ) as GenreAvgVoteCount " \
"where m.apiId=mg.apiId and a.actorId=am.actorId and am.filmId=m.apiId and g.genreName=%s " \
"and temp.id=mg.genreId and m.voteCount>GenreAvgVoteCount.avgvotecount and m.voteAvg>8 " \
"and temp.id= GenreAvgVoteCount.genreId " \
"group by a.actorId " \
"union " \
"select a.actorName as name, count(*) as cnt " \
"from Actors a, ShowGenre mg, Shows m , ActorsShow am , Genre g , " \
"(select Genre.genreId as id from Genre where genreName=%s) AS temp, " \
"(select g.genreId,g.genreName,avg(voteCount) as avgvotecount from Shows m , Genre g, ShowGenre mg " \
"where m.apiId=mg.apiId and g.genreId=mg.genreId group by g.genreId ) as GenreAvgVoteCount " \
"where m.apiId=mg.apiId and a.actorId=am.actorId and am.showId=m.apiId and g.genreName=%s " \
"and temp.id=mg.genreId and m.voteCount>GenreAvgVoteCount.avgvotecount and m.voteAvg>8 " \
"and temp.id= GenreAvgVoteCount.genreId " \
"group by a.actorId) as temp " \
"order by -cnt;"
res = select(sqlQuery, [title, title, title, title])
result = [{res['headers'][0]: row[0]} for row in res['rows']]
return result
except sql_executor.NoResultsException:
return []
def getBasicInfo(apiId):
sqlQuery = "select * from Movie where Movie.apiId=%s"
resofMovie = select(sqlQuery, apiId)
resultTitle = [{resofMovie['headers'][1]: row[1]} for row in resofMovie['rows']]
resultTitle = resultTitle[0]['title']
imdb = [{resofMovie['headers'][8]: row[8]} for row in resofMovie['rows']]
if imdb[0]['imdbId']!=None:
resultimdb = "https://www.imdb.com/title/" + imdb[0]['imdbId']
else:
resultimdb=""
length = [{resofMovie['headers'][4]: row[4]} for row in resofMovie['rows']]
length = length[0]['length']
collection = [{resofMovie['headers'][7]: row[7]} for row in resofMovie['rows']]
collection = collection[0]['collection']
if collection is not None:
collection = "<b>Collection</b>: " + collection
else:
collection = ""
webSite = [{resofMovie['headers'][9]: row[9]} for row in resofMovie['rows']]
webSite = webSite[0]['homePage']
if webSite != "":
webSite = "<b>WebSite</b>: " + webSite
vote = [{resofMovie['headers'][13]: row[13]} for row in resofMovie['rows']]
vote = vote[0]['voteAvg']
return resultTitle,resultimdb,length,collection,webSite,vote;
def getSimilarMovie(apiId):
imagers1 = ""
links1=""
try:
sqlQuery = "select distinct commonMovie.id ,pm.image from (SELECT m2.apiId as" \
" id,m2.title as title, count(*) as count FROM Movie as m, Movie as " \
"m2, Actors as a, ActorsMovie as am, ActorsMovie as am2 WHERE m.apiId=%s" \
" AND am.filmId<>am2.filmId AND am.filmId=m.apiId AND am.actorId=a.actorId" \
" AND am2.filmId=m2.apiId AND am.actorId=am2.actorId AND m.langId=m2.langId " \
"GROUP BY m2.apiId,m2.title) as commonMovie , (SELECT distinct m2.apiId as " \
"id, m2.title as title, count(*) as count FROM Movie as m, Movie as m2, Genre " \
"as g , MoviesGenre as mg, MoviesGenre as mg2 WHERE m.apiId=%s AND mg.apiId<>mg2.apiId" \
" AND mg.apiId=m.apiId AND mg.genreId=g.genreId AND mg2.apiId=m2.apiId AND" \
" mg.genreId=mg2.genreId GROUP BY m2.apiId,m2.title) as commonGenre , Movie" \
" m1,PosterMovie pm where commonMovie.count >3 and commonGenre.count>2 " \
"and m1.apiId=commonMovie.id and m1.apiId=commonGenre.id and commonMovie.id= pm.apiId "
similarMovie = select(sqlQuery, [apiId, apiId])
resultS = [{similarMovie['headers'][0]: row[0],
similarMovie['headers'][1]: row[1]} for row in similarMovie['rows']]
shuffle(resultS)
imagers1 = resultS[0]['image']
links1 = resultS[0]['id']
if imagers1 == None:
imagers1 = ""
except sql_executor.NoResultsException:
return imagers1, links1
return imagers1,links1
def search_foreign_languages():
if request.method == 'POST':
title = request.form['dropdown']
sqlQueryMovie = "select m.title,m.popularity from Movie m , Language l where m.langId=l.languageId and l.LangName= %s order by - m.popularity"
sqlQueryShow = "select s.title , s.popularity from Shows s, Language l where s.langId = l.languageId and l.langName = %s order by - s.popularity"
sqlQuery = "select distinct Language.langName as language from Language order by language DESC"
resLang = select(sqlQuery)
languages = [row[0] for row in resLang["rows"]]
try:
resMovie = select(sqlQueryMovie, title)
resultMovie = [{
resMovie['headers'][0]: row[0],
resMovie['headers'][1]: row[1]
} for row in resMovie['rows']]
try:
resShow = select(sqlQueryShow, title)
resultShow = [{
resShow['headers'][0]: row[0],
resShow['headers'][1]: row[1]
} for row in resShow['rows']]
return render_template('Foreign-Languages.html',
resMovie=resultMovie,
resShow=resultShow,
languages=languages)
except sql_executor.NoResultsException:
return render_template('Foreign-Languages.html',
resMovie=resultMovie,
resShow=None,
languages=languages)
except sql_executor.NoResultsException:
try:
resShow = select(sqlQueryShow, title)
resultShow = [{
resShow['headers'][0]: row[0],
resShow['headers'][1]: row[1]
} for row in resShow['rows']]
return render_template('Foreign-Languages.html',
resMovie=None,
resShow=resultShow,
languages=languages)
except sql_executor.NoResultsException:
return render_template('Foreign-Languages.html',
languages=languages)
def getBasicInfo(apiId):
sqlQuery = "select title from Shows where apiId=%s"
resTitle = select(sqlQuery, apiId)
resultTitle = [{resTitle['headers'][0]: row[0]} for row in resTitle['rows']]
resultTitle = resultTitle[0]['title']
sqlQuery = "select * from Shows where Shows.apiId=%s"
resofShows = select(sqlQuery, apiId)
length = [{resofShows['headers'][4]: row[4]} for row in resofShows['rows']]
length = length[0]['length']
webSite = [{resofShows['headers'][5]: row[5]} for row in resofShows['rows']]
webSite = webSite[0]['homePage']
if webSite != "":
webSite = "<b>WebSite</b>: " + webSite
vote = [{resofShows['headers'][9]: row[9]} for row in resofShows['rows']]
vote = vote[0]['voteAvg']
seasons = [{resofShows['headers'][10]: row[10]} for row in resofShows['rows']]
seasons = seasons[0]['seasons']
return resultTitle,length,webSite,vote,seasons
def getDirector(apiId):
sqlQuery = "select d.directorName from DirectorsMovie dm , Directors d where dm.filmId=%s and d.directorId=dm.directorId"
MovieDirector = select(sqlQuery, apiId)
director = [{MovieDirector['headers'][0]: row[0]} for row in MovieDirector['rows']]
director = director[0]['directorName']
if director is not None:
director = "<b>Director</b>: " + director
else:
director = ""
return director
def getProducer(apiId):
sqlQuery = "select p.producerName from ProducersShow ps , Producers p where ps.ShowId=%s and p.producerId=ps.producerId"
ShowPrducer = select(sqlQuery, apiId)
producer = [{ShowPrducer['headers'][0]: row[0]} for row in ShowPrducer['rows']]
producer = producer[0]['producerName']
if producer is not None:
producer = "<b>Producer</b>: " + producer
else :
producer = ""
return producer
def getMediaByActor(actor):
sqlQuery = "(select distinct Movie.title, 'Movie' as Media from Movie, ActorsMovie, Actors Where Actors.actorName = %s and Movie.apiId = ActorsMovie.filmId and ActorsMovie.actorId = Actors.actorId order by Movie.popularity) union (select distinct Shows.title, 'Series' as Media from Shows, ActorsShow, Actors Where Actors.actorName = %s and Shows.apiId = ActorsShow.showId and ActorsShow.actorId = Actors.actorId order by Shows.popularity)"
try:
res = select(sqlQuery, [actor, actor])
result = [{
res['headers'][0]: row[0],
res['headers'][1]: row[1]
} for row in res['rows']]
return result
except sql_executor.NoResultsException:
return []
def getKnownActors(date):
moviesNumber = 3
tempStart = date[0].split("/")
tempEnd = date[1].split("/")
start = tempStart[2] + "-" + tempStart[0] + "-" + tempStart[1]
end = tempEnd[2] + "-" + tempEnd[0] + "-" + tempEnd[1]
sqlQuery = "select title, cnt from (select title,cnt from (select a1.actorName as title, count(*) as cnt, a1.actorId as id from Movie m1 , Actors a1 , ActorsMovie am1 where a1.actorId=am1.actorId and m1.apiId=am1.filmId and m1.releaseDay between %s and %s group by a1.actorId having count(*)>%s) as temp group by temp.id order by temp.cnt DESC) as temp4 union (select title,cnt from (select a1.actorName as title, count(*) as cnt, a1.actorId as id from Shows m1 , Actors a1 , ActorsShow am1 where a1.actorId=am1.actorId and m1.apiId=am1.showId and m1.releaseDay between %s and %s group by a1.actorId having count(*)>%s) as temp2 group by temp2.id order by temp2.cnt DESC)"
try:
res4 = select(sqlQuery,
[start, end, moviesNumber, start, end, moviesNumber])
result4 = [{res4['headers'][0]: row[0]} for row in res4['rows']]
return result4
except sql_executor.NoResultsException:
return []
def getCast(apiId):
sqlQuery = "select a.actorName from ActorsMovie am , Actors a where filmId=%s and a.actorId=am.actorId"
MovieActors = select(sqlQuery, apiId)
result = [{MovieActors['headers'][0]: row[0]} for row in MovieActors['rows']]
credit = ''
first = True
for actor in result:
if first == False:
credit = credit + "," + actor['actorName']
if first == True:
credit = actor['actorName']
first = False
if credit != None:
credit = "<b>Cast :</b> " + credit
return credit
def getSpokenLang(apiId):
sqlQuery = "select l.LangName from LanguageMovie lm ,Language l where lm.movieId=%s and l.languageId=lm.languageId"
LanguagesMovie = select(sqlQuery, apiId)
result = [{LanguagesMovie['headers'][0]: row[0]} for row in LanguagesMovie['rows']]
first = True;
for lang in result:
if first == False:
movielang = movielang + "," + lang['LangName']
if first == True:
movielang = lang['LangName']
first = False
if movielang != None:
movielang = "<b>Spoken Language :</b> " + movielang;
else:
movielang=""
return movielang
def getSpokenLang(apiId):
sqlQuery = "select l.LangName from LanguageShow lm ,Language l where lm.showId=%s and l.languageId=lm.languageId"
LanguagesShow = select(sqlQuery, apiId)
result = [{LanguagesShow['headers'][0]: row[0]} for row in LanguagesShow['rows']]
first = True;
showlang = ""
for lang in result:
if first == False:
showlang = showlang + "," + lang['LangName']
if first == True:
showlang = lang['LangName']
first = False
if showlang != None:
showlang = "<b>Spoken Language :</b> " + showlang;
else:
showlang=""
return showlang
def getComputeMovie(apiId):
imagerc1=""
linkc1=""
try :
sqlQuery ="select distinct m2.apiId ,pm.image from Movie m1,MoviesGenre mg1 , Movie m2,MoviesGenre" \
" mg2 , PosterMovie pm where m1.apiId=mg1.apiId and (m2.releaseDay between " \
"m1.releaseDay - interval 6 month and m1.releaseDay or m2.releaseDay between m1.releaseDay" \
" and m1.releaseDay + interval 6 month )and m2.apiId=mg2.apiId and mg1.genreId=mg2.genreId" \
" and m2.langId= m1.langId and m1.apiId=%s and m1.apiId <>m2.apiId and pm.apiId=m2.apiId "
commptiveMovie = select(sqlQuery, apiId)
resultM = [{commptiveMovie['headers'][0]: row[0],
commptiveMovie['headers'][1]: row[1]} for row in commptiveMovie['rows']]
shuffle(resultM)
imagerc1 = resultM[0]['image']
linkc1 = resultM[0]['apiId']
if imagerc1 == None:
imagerc1 = ""
except sql_executor.NoResultsException:
return imagerc1, linkc1
return imagerc1, linkc1
def getSyccesfulActors(date):
moviesNumber2 = 3
revenueToBudeget = 6.5
popularity = 100
tempStart = date[0].split("/")
tempEnd = date[1].split("/")
start = tempStart[2] + "-" + tempStart[0] + "-" + tempStart[1]
end = tempEnd[2] + "-" + tempEnd[0] + "-" + tempEnd[1]
sqlQuery = "select distinct title from" \
"(select title from" \
"( select title, cnt from" \
"(select a1.actorName as title, count(*) as cnt, a1.actorId as id " \
"from Movie m1 , Actors a1 , ActorsMovie am1 " \
"where a1.actorId=am1.actorId and m1.apiId=am1.filmId and m1.releaseDay " \
"between %s and %s and ((m1.budget*%s)<m1.revenue) and (m1.popularity>%s) " \
"group by a1.actorId having count(*)>%s) as temp group by temp.id order by temp.cnt DESC) as temp " \
"union " \
"select title from" \
"( select title, cnt from" \
"(select a1.actorName as title, count(*) as cnt, a1.actorId as id " \
"from Shows m1 , Actors a1 , ActorsShow am1 " \
"where a1.actorId=am1.actorId and m1.apiId=am1.showId and m1.releaseDay " \
"between %s and %s and (m1.popularity>%s) " \
"group by a1.actorId having count(*)>%s) as temp2 " \
"group by temp2.id " \
"order by temp2.cnt DESC) as temp4) as final"
try:
res3 = select(sqlQuery, [
start, end, revenueToBudeget, popularity, moviesNumber2, start,
end, popularity, moviesNumber2
])
result3 = [{res3['headers'][0]: row[0]} for row in res3['rows']]
return result3
except sql_executor.NoResultsException:
return []
def getSimilarShow(apiId):
imagers1=""
links1=""
try :
sqlQuery = "select distinct commonShow.id ,pm.image from (SELECT m2.apiId as id,m2.title as title, " \
"count(*) as count FROM Shows as m, Shows as m2, Actors as a, ActorsShow as am, ActorsShow " \
"as am2 WHERE m.apiId=%s AND am.showId<>am2.showId AND am.showId=m.apiId AND am.actorId=a.actorId " \
" AND am2.showId=m2.apiId AND am.actorId=am2.actorId AND m.langId=m2.langId GROUP BY m2.apiId,m2.title) " \
"as commonShow , (SELECT distinct m2.apiId as id, m2.title as title, count(*) as count FROM Shows as m," \
" Shows as m2, Genre as g , ShowGenre as mg, ShowGenre as mg2 WHERE m.apiId=%s AND mg.apiId<>mg2.apiId AND " \
" mg.apiId=m.apiId AND mg.genreId=g.genreId AND mg2.apiId=m2.apiId AND mg.genreId=mg2.genreId GROUP BY " \
"m2.apiId,m2.title) as commonGenre , Shows m1,PosterShow pm where commonShow.count >=1 and commonGenre.count>=1 " \
" and m1.apiId=commonShow.id and m1.apiId=commonGenre.id and commonShow.id= pm.apiId "
similarShow = select(sqlQuery, [apiId, apiId])
resultS = [{similarShow['headers'][0]: row[0],
similarShow['headers'][1]: row[1]} for row in similarShow['rows']]
shuffle(resultS)
imagers1 = resultS[0]['image']
links1 = resultS[0]['id']
if imagers1 == None:
imagers1=""
except sql_executor.NoResultsException:
pass
return imagers1,links1
def Actors():
sqlQuery = "select distinct Genre.genreName as title from Genre"
res = select(sqlQuery)
result = [k[0] for k in res["rows"]]
return render_template('Actors.html', genres=result)
def Foreign_Languages():
sqlQurey = "select distinct Language.langName as language from Language order by language DESC"
res = select(sqlQurey)
result = [row[0] for row in res['rows']]
return render_template('Foreign-Languages.html', languages=result)
def getPoster(apiId):
sqlQuery = "select image from PosterShow where apiId=%s"
resimage = select(sqlQuery, apiId)
resultimage = [{resimage['headers'][0]: row[0]} for row in resimage['rows']]
resultimage = resultimage[0]['image']
return resultimage;
def getOverView(apiId):
sqlQuery = "select overview from ShowOverview where ShowOverview.showId=%s"
resOverView = select(sqlQuery, apiId)
resultOverview = [{resOverView['headers'][0]: row[0]} for row in resOverView['rows']]
resultOverview = resultOverview[0]['overview']
return resultOverview
