def get(self):
offset = request.args.get('offset')
offset = 0 if offset is None else offset
max_results = request.args.get('maxResults')
max_results = 20 if max_results is None else max_results
connection = sqlhelper.get_sql_conn()
cursor = connection.cursor()
reviews_query = query.Query()
reviews_query.set_table("Reviews")
reviews_query.set_return_columns([
"Reviews.Id", "MovieId", "Movies.Name", "DateTime", "ReviewStats"
])
reviews_query.add_inner_join('MovieId', 'Movies', 'Id')
reviews_query.set_order_by_columns(['DateTime'])
reviews_query.set_results_order(query.ResultsOrder.DESC)
reviews_query.set_results_offset(offset)
reviews_query.set_max_results(max_results)
print(reviews_query.to_sql_query())
cursor.execute(reviews_query.to_sql_query())
reviews = cursor.fetchall()
return {'reviews': reviews_to_json(reviews)}
def delete(self, id):
connection = sqlhelper.get_sql_conn()
cursor = connection.cursor()
sql = 'DELETE FROM boxofficementat.Reviews WHERE MovieId=%s'
cursor.execute(sql, id)
connection.commit()
connection.close()
def get(self):
connection = sqlhelper.get_sql_conn()
cursor = connection.cursor()
studios_query = query.Query()
studios_query.set_table("Movies")
studios_query.set_unique_results(True)
studios_query.set_return_columns(["MpaaRating"])
print(studios_query.to_sql_query())
cursor.execute(studios_query.to_sql_query())
return {'ratings': [i[0] for i in cursor.fetchall()]}
def post(self):
movie_id = request.form['movieId']
date_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
review_text = request.form['reviewText']
review_stats = """{ "overall": 3 }"""
params = (movie_id, date_time, review_text, review_stats)
connection = sqlhelper.get_sql_conn()
cursor = connection.cursor()
sql = 'INSERT INTO boxofficementat.Reviews (MovieId, DateTime, ReviewText, ReviewStats) VALUES(%s, %s, %s, %s)'
cursor.execute(sql, params)
connection.commit()
connection.close()
def get(self, id):
connection = sqlhelper.get_sql_conn()
cursor = connection.cursor()
person_query = query.Query()
person_query.set_table("People")
person_query.add_where_clause(condition.Condition('Id', '=', id))
command = person_query.to_sql_query()
cursor.execute(command)
person = cursor.fetchone()
if person is None:
abort(404, "Person {0} doesn't exist.".format(id))
return person_to_json(person)
def get(self, id):
connection = sqlhelper.get_sql_conn()
cursor = connection.cursor()
studio_query = query.Query()
studio_query.set_table("Studios")
studio_query.set_unique_results(True)
studio_query.set_return_columns(["Id", "Name"])
studio_query.add_where_clause(condition.Condition('Id', '=', id))
cursor.execute(studio_query.to_sql_query())
studio = cursor.fetchone()
if studio is None:
abort(404, "Studio {0} doesn't exist.".format(id))
return studio_to_json(studio)
def get(self, id):
connection = sqlhelper.get_sql_conn()
cursor = connection.cursor()
movie_query = query.Query()
movie_query.set_table("Movies")
movie_query.add_where_clause(condition.Condition('Id', '=', id))
command = movie_query.to_sql_query()
cursor.execute(command)
movie = cursor.fetchone()
if movie is None:
abort(404, "Movie {0} doesn't exist.".format(id))
box_office_query = query.Query()
box_office_query.set_table("DomesticBoxOffice")
box_office_query.add_where_clause(
condition.Condition('MovieId', '=', id))
cursor.execute(box_office_query.to_sql_query())
weeks = []
for record in cursor.fetchall():
week_number = int(record[0][record[0].index('_') + 1:])
if week_number != 0:
start_date = datetime.strftime(record[2], '%Y-%m-%d')
end_date = datetime.strftime(record[3], '%Y-%m-%d')
gross = int(record[4])
theater_count = int(record[5])
weeks.append(
[week_number, start_date, end_date, gross, theater_count])
studio_name_query = query.Query()
studio_name_query.set_table("Studios")
studio_name_query.add_where_clause(
condition.Condition('Id', '=', movie[2]))
studio_name_query.set_return_columns(['Name'])
cursor.execute(studio_name_query.to_sql_query())
studio_name = cursor.fetchone()[0]
has_review_query = query.Query()
has_review_query.set_table("Reviews")
has_review_query.add_where_clause(
condition.Condition('MovieId', '=', id))
cursor.execute(has_review_query.to_sql_query())
has_review = False if cursor.fetchone() is None else True
return movie_to_json(movie, weeks, studio_name, has_review)
def get(self, movie_id):
connection = sqlhelper.get_sql_conn()
cursor = connection.cursor()
credits_query = query.Query()
credits_query.set_table("Credits")
credits_query.set_return_columns(
["PersonId", "People.Name", "Relationship"])
credits_query.add_inner_join("PersonId", "People", "Id")
credits_query.add_where_clause(
condition.Condition("MovieId", "=", movie_id))
command = credits_query.to_sql_query()
cursor.execute(command)
credits = cursor.fetchall()
return {'credits': [credit_to_json(credit) for credit in credits]}
def get(self, id):
connection = sqlhelper.get_sql_conn()
cursor = connection.cursor()
review_query = query.Query()
review_query.set_table("Reviews")
review_query.set_unique_results(True)
review_query.set_return_columns([
"Reviews.Id", "MovieId", "Movies.Name", "DateTime", "ReviewText",
"ReviewStats"
])
review_query.add_inner_join('MovieId', 'Movies', 'Id')
review_query.add_where_clause(condition.Condition(
'Movies.Id', '=', id))
cursor.execute(review_query.to_sql_query())
review = cursor.fetchone()
if review is None:
abort(404, "Review {0} doesn't exist.".format(id))
return review_to_json(review)
def get(self):
offset = request.args.get('offset')
offset = 0 if offset is None else offset
max_results = request.args.get('maxResults')
max_results = 2000 if max_results is None else max_results
name = request.args.get('name')
connection = sqlhelper.get_sql_conn()
cursor = connection.cursor()
search_query = query.Query()
search_query.set_table("People")
search_query.add_where_clause(condition.Condition("Name", "LIKE", "%" + name + "%"))
mode = request.args.get('mode')
if mode is not None and mode != 'results':
search_query.set_mode(mode)
if mode == 'count':
search_query.add_aggregate_column(AggregateType.COUNT, 'Id', True)
else:
max_results = request.args.get('maxResults')
if max_results is not None:
search_query.set_max_results(max_results)
offset = request.args.get('offset')
if offset is not None:
search_query.set_results_offset(offset)
search_query.set_order_by_columns(["Name"])
include_limit = False if mode == 'count' else True
command = search_query.to_sql_query(include_limit)
cursor.execute(command)
if mode == 'count':
count = cursor.fetchone()
return {'count': count[0]}
else:
people = cursor.fetchall()
return {'people': [person_to_json(person) for person in people]}
def get(self):
offset = request.args.get('offset')
offset = 0 if offset is None else offset
max_results = request.args.get('maxResults')
max_results = 2000 if max_results is None else max_results
connection = sqlhelper.get_sql_conn()
cursor = connection.cursor()
studios_query = query.Query()
studios_query.set_table("Movies")
studios_query.set_return_columns(["Studios.Id", "Studios.Name"])
studios_query.add_inner_join('Studio', 'Studios', 'Id')
studios_query.add_aggregate_column(query.AggregateType.COUNT,
'Movies.Id', True)
studios_query.set_order_by_columns(['COUNT'])
studios_query.set_results_order(query.ResultsOrder.DESC)
studios_query.set_results_offset(offset)
studios_query.set_max_results(max_results)
cursor.execute(studios_query.to_sql_query())
studios = cursor.fetchall()
return {'studios': [studio_to_json(studio) for studio in studios]}
def get(self):
connection = sqlhelper.get_sql_conn()
cursor = connection.cursor()
latest_query = query.Query()
latest_query.set_table('DomesticBoxOffice')
latest_query.add_inner_join('MovieId', 'Movies', 'Id')
latest_query.set_return_columns([
'DomesticBoxOffice.Id', 'MovieId', 'Movies.Name', 'StartDate',
'EndDate', 'Gross', 'TheaterCount'
])
last_week_query = query.Query()
last_week_query.set_table('DomesticBoxOffice')
last_week_query.add_aggregate_column(AggregateType.MAX, 'EndDate')
last_week_query.set_mode('max')
latest_query.add_subquery('EndDate', last_week_query)
command = latest_query.to_sql_query(include_limit=False)
cursor.execute(command)
records = cursor.fetchall()
return {'records': [record_to_json(record) for record in records]}
def test_sql_conn():
conn = sqlhelper.get_sql_conn()
assert conn is not None
host_info = conn.get_host_info()
assert host_info == 'socket boxofficementat.cwokc1guxfkk.us-west-2.rds.amazonaws.com:3306'
def get(self):
connection = sqlhelper.get_sql_conn()
cursor = connection.cursor()
movies_query = query.Query()
movies_query.set_table("Movies")
title = request.args.get('title')
if title is not None:
movies_query.add_where_clause(
condition.Condition('Name', 'LIKE', "%" + title + "%"))
studio = request.args.get('studio')
if studio is not None:
movies_query.add_where_clause(
condition.Condition('Studio', '=', studio))
genre = request.args.get('genre')
if genre is not None:
movies_query.add_where_clause(
condition.Condition('Genre', 'LIKE', "%" + genre + "%"))
rating = request.args.get('rating')
if rating is not None:
movies_query.add_where_clause(
condition.Condition('MpaaRating', '=', rating))
release_year = request.args.get('releaseYear')
if release_year is not None:
release_year = int(release_year)
movies_query.add_where_clause(
condition.Condition('YEAR(ReleasedDate)', '=', release_year))
release_month = request.args.get('releaseMonth')
if release_month is not None:
release_month = int(release_month)
movies_query.add_where_clause(
condition.Condition('MONTH(ReleasedDate)', '=', release_month))
release_day = request.args.get('releaseDay')
if release_day is not None:
release_day = int(release_day)
movies_query.add_where_clause(
condition.Condition('DAY(ReleasedDate)', '=', release_day))
person = request.args.get('person')
if person is not None:
subquery = query.Query()
subquery.set_table("Credits")
subquery.set_return_columns(["MovieId"])
subquery.add_where_clause(
condition.Condition("PersonId", "=", person))
movies_query.add_subquery("Id", subquery)
mode = request.args.get('mode')
if mode is not None and mode != 'results':
movies_query.set_mode(mode)
if mode == 'count':
movies_query.add_aggregate_column(AggregateType.COUNT, 'Id',
True)
else:
max_results = request.args.get('maxResults')
if max_results is not None:
movies_query.set_max_results(max_results)
offset = request.args.get('offset')
if offset is not None:
movies_query.set_results_offset(offset)
movies_query.set_order_by_columns(['DomesticGross'])
movies_query.set_results_order(ResultsOrder.DESC)
include_limit = False if mode == 'count' else True
command = movies_query.to_sql_query(include_limit)
cursor.execute(command)
if mode == 'count':
count = cursor.fetchone()
return {'count': count[0]}
else:
movies = cursor.fetchall()
return {'movies': [movie_to_json(movie) for movie in movies]}
