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