def update_user(user_id):
c = backend.get_db().cursor()
data = request.json
schema = backend.get_schema('users')
for key, value in data.items():
if key != 'skills':
# Don't do anything if the key isn't valid
if key not in schema:
continue
c.execute('UPDATE users SET ' + key + ' = ? WHERE id = ?',
[value, user_id])
else:
for skill in data[key]:
skill_ratings = backend.query_db(
'SELECT * FROM skills WHERE user_id=? AND name=?',
[user_id, skill['name']])
if len(skill_ratings) == 0:
# If the skill doesn't exist, insert into the skills table
c.execute(
'''INSERT INTO skills (name, rating, user_id)
VALUES (?, ?, ?)''',
[skill['name'], skill['rating'], user_id])
else:
# Otherwise, update the rating for the appropriate skill
c.execute(
'UPDATE skills SET rating=? WHERE user_id=? AND name=?',
[skill['rating'], user_id, skill['name']])
backend.get_db().commit()
user = backend.query_db('SELECT * FROM users WHERE id = ?', [user_id],
one=True)
user_skills = backend.query_db('select * from skills where user_id = ?',
[user_id])
backend.helpers.format_user(user_skills, user)
return jsonify(user)
def update_user():
print(current_app.login_manager.login_view)
ks = {'username','password','nick_name','age','gender','street',\
'suberb','city','postcode','description'}
if ks.issuperset(set(request.form.keys())):
values = ""
for k in request.form.keys():
if k == 'age' or k == 'gender':
v = escape_string(request.form[k])
values += f"{k} = {v if v else 'NULL'},"
elif k == 'password':
hash = pbkdf2_sha512.hash(request.form[k])
values += f"{k} = '{hash}',"
else:
values += f"{k} = '{escape_string(request.form[k])}',"
values = values[:-1]
try:
db = get_db(current_app)
with db.cursor() as c:
sql = " UPDATE `filmfinder`.`users` SET %s WHERE user_id = '%s';"
print(sql % (values, current_user.get_id()))
c.execute(sql % (values, current_user.get_id()))
db.commit()
except MySQLError as err:
db.rollback()
print(err)
else:
if "password" in request.form.keys():
logout_user()
return redirect(current_app.login_manager.login_view)
else:
return f"OK", 200
abort(400)
def create_user():
ks = {'username','password','nick_name','age','gender','street',\
'suberb','city','postcode','description'}
if set(request.form.keys()).issuperset(
ks) and request.form["username"] and request.form["password"]:
rows = ""
values = ""
for k in ks:
rows += f"`{k}`,"
if k == 'age' or k == 'gender':
v = escape_string(request.form[k])
values += f"{v if v else 'NULL'},"
elif k == 'password':
hash = pbkdf2_sha512.hash(request.form[k])
values += f"'{hash}',"
else:
values += f"'{escape_string(request.form[k])}',"
rows = rows[:-1]
values = values[:-1]
try:
db = get_db(current_app)
with db.cursor() as c:
sql = " INSERT INTO `filmfinder`.`users`(%s) VALUES (%s);"
print(sql % (rows, values))
c.execute(sql % (rows, values))
db.commit()
except MySQLError as err:
db.rollback()
print(err)
else:
return f"OK", 200
abort(400)
def check_username():
if request.args["username"]:
try:
db = get_db(current_app)
with db.cursor() as c:
sql = "select 1 from `filmfinder`.`users` where username = %s;"
c.execute(sql, request.args["username"])
if c.rowcount == 1:
return jsonify({"confict_name": 1})
else:
return jsonify({"confict_name": 0})
except MySQLError as err:
print(err)
abort(400)
def add_response():
printR(request)
"""
Description
-----------
this function add response to other commnet
Parameter
---------
@response_id: responding to which comment. It must not be empty.
@comment: comment text.It must not be empty.
@ancestor_comment_id: under which rated comment
Returns
-------
"Ok", 200
"bad request", 400
"""
if {'response_id','comment'} == set(request.form.keys()):
for k in {'response_id','comment'}:
if not request.form[k]:
abort(400)
comment_values = ""
comment_values += f"'{escape_string(current_user.get_id())}',"
# comment_values += f"{escape_string(request.form['movie_id'])},"
comment_values += f"'{escape_string(request.form['comment'])}'"
# comment_values += f"{escape_string(request.form['ancestor_comment_id'])}"
try:
db = get_db(current_app)
with db.cursor() as c:
#insert cooment
sql = f" INSERT INTO `filmfinder`.`userXcomment`(user_id,comment,ancestor_comment_id) select %s,ancestor_comment_id from filmfinder.userXcomment where comment_id = {request.form['response_id']};"
print(sql%(comment_values))
c.execute(sql%(comment_values))
#insert response relationship
sql = " INSERT INTO `filmfinder`.`commentXresponse`(comment_id,response_to) VALUES (%s,%s);"
print(sql%(c.lastrowid,request.form['response_id']))
c.execute(sql%(c.lastrowid,request.form['response_id']))
except MySQLError as err:
db.rollback()
print(err)
else:
db.commit()
return f"OK",200
abort(400)
def rm_blocklist():
if "user_id" in request.form:
try:
db = get_db(current_app)
with db.cursor() as c:
sql = "DELETE FROM `filmfinder`.`blocklist` WHERE user_id = %s and blocklisted_user = %s;"
# print(sql%(rows,values))
c.execute(sql,(current_user.get_id(),request.form['user_id']))
db.commit()
except MySQLError as err:
db.rollback()
print(err)
else:
return f"OK",200
abort(400)
def add_blocklist():
if "user_id" in request.form and current_user.get_id() != request.form['user_id']:
try:
db = get_db(current_app)
with db.cursor() as c:
sql = " INSERT INTO `filmfinder`.`blocklist`(`user_id`,`blocklisted_user`) VALUES (%s,%s);"
print(sql%(current_user.get_id(),request.form['user_id']))
c.execute(sql,(current_user.get_id(),request.form['user_id']))
db.commit()
except MySQLError as err:
db.rollback()
print(err)
else:
return f"OK",200
abort(400)
def get_user_by_id(user_id):
try:
sql = (
"SELECT `users`.`user_id`,`users`.`username`,`users`.`nick_name`, `users`.`age`,`users`."
"`gender`,`users`.`street`,`users`.`suberb`,`users`.`city`,`users`.`postcode`,`users`.`description`"
"FROM `filmfinder`.`users`"
"Where user_id = %s")
db = get_db(current_app)
with db.cursor() as c:
c.execute(sql, (user_id))
return jsonify(c.fetchone())
except MySQLError as err:
print(err)
return abort(500)
def login():
if request.form["username"] and request.form["password"]:
try:
sql = (
"SELECT `users`.`user_id`,`users`.`password`,`users`.`username`,`users`.`nick_name`, `users`.`age`,`users`."
"`gender`,`users`.`street`,`users`.`suberb`,`users`.`city`,`users`.`postcode`,`users`.`description`"
"FROM `filmfinder`.`users`"
"Where username = %s;")
db = get_db(current_app)
name = escape_string(request.form["username"])
with db.cursor() as c:
print(sql % name)
c.execute(sql, name)
if c.rowcount == 1:
row = c.fetchone()
if pbkdf2_sha512.verify(request.form["password"],
row['password']):
user = User(**row)
login_user(user)
return jsonify({"login_status": 1})
except MySQLError as err:
print(err)
abort(401)
def get_blocklist():
if "page" in request.args and "page_size" in request.args:
size = 50
if "page_size" in request.args:
size = int(escape_string(request.args['page_size']))
if size >50:
size = 50
limit = f"limit {size}"
if "page" in request.args:
offset = (int(escape_string(request.args['page'])) -1 )* size
limit += f" OFFSET {offset}"
try:
db = get_db(current_app)
with db.cursor() as c:
sql = f""" SELECT `users`.`user_id`,
`users`.`username`,
`users`.`nick_name`,
`users`.`age`,
`users`.`gender`,
`users`.`street`,
`users`.`suberb`,
`users`.`city`,
`users`.`postcode`,
`users`.`description`
from `filmfinder`.`blocklist` as b left join `filmfinder`.`users` on b.blocklisted_user = users.user_id where b.user_id = %s {limit};"""
print(sql%(current_user.get_id()))
c.execute(sql,(current_user.get_id()))
result = c.fetchall()
return jsonify(result)
except MySQLError as err:
db.rollback()
print(err)
else:
return f"OK",200
abort(400)
def create_user():
c = backend.get_db().cursor()
data = request.json
schema = backend.get_schema('users')
# Since skills is not in table schema
schema.append('skills')
schema.remove('id')
keys = set(data.keys())
schema_set = set(schema)
# If the data is missing keys, abort
if schema_set.difference(keys) != set():
abort(400)
### Data sanity checks ###
phone_exp = re.compile(
"^(?:\+?1[-. ]?)?\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$")
email_exp = re.compile("^[\w\-\.]+@([\w-]+\.)+[\w-]{2,4}$")
# Check if name, picture and company are strings
if (not isinstance(data["name"], str)
or not isinstance(data["picture"], str)
or not isinstance(data["company"], str)):
abort(400)
# Check if phone number has a valid format
if not phone_exp.match(data["phone"]):
abort(400)
# Check if email address has a valid format
if not email_exp.match(data["email"]):
abort(400)
# Check if latitude and longitude are floats
try:
latitude = float(data["latitude"])
longitude = float(data["longitude"])
except ValueError as err:
abort(400)
# Check if skills is a list
if not isinstance(data["skills"], list):
abort(400)
tup = (data["name"], data["picture"], data["company"], data["email"],
data["phone"], data["latitude"], data["longitude"])
c.execute(
'''INSERT INTO users
(name, picture, company, email, phone, latitude, longitude)
VALUES (?, ?, ?, ?, ?, ?, ?)''', tup)
# The last row id will be the user_id for the skill
last_row = c.lastrowid
skills_to_append = []
for skill in data["skills"]:
if (not isinstance(skill.get("name"), str)
or not isinstance(skill.get("rating"), int)):
abort(400)
skills_to_append.append((skill["name"], skill["rating"], last_row))
c.executemany(
'''INSERT INTO skills (name, rating, user_id)
VALUES (?, ?, ?) ''', skills_to_append)
backend.get_db().commit()
user = backend.query_db('SELECT * FROM users WHERE id = ?', [last_row],
one=True)
user_skills = backend.query_db('SELECT * FROM skills WHERE user_id = ?',
[last_row])
backend.helpers.format_user(user_skills, user)
return jsonify(user)
def top():
"""
Description
-----------
This function returns top n films which rank by filmfinder score.
If the user have logined in, the tailored ranking will be returned
Parameters
----------
@min_date_published (optional,imdb col): YYYY-MM-DD hh:mm:ss ', default "2019-01-01"
@max_date_published (optional, imdb col): YYYY-MM-DD hh:mm:ss ', optional
@min_votes (optional,imdb col): for more acurate ranking, you set minmun votes for the film. default 2000
@genre[](otional): send as comma separated string. e.g. in ajax "1,2,3" -> in python [1,2,3]
returns rank for given each genre,
if wrong genre or empyt genre is sent, it returns ranking for all genre
@page_size (required): request as top n film, if size greater than 50, size = 50
Returns
-------
top N movies in each genre in json
@filmfinder_score: score which calculate by our algorithm,
if user has logined in, tailored results will be returned.
imdb_title_id,
@title`,
@original_title`,
@date_published`,
@genre`,
@duration`,
@country`,
@language`,
@director`,
@writer`,
@production_company`,
@actors`,
@description`,
@avg_vote`,
@votes`,
@budget`,
@usa_gross_income`,
@worlwide_gross_income`,
@metascore`,
@reviews_from_users`,
@reviews_from_critics`,
@year`,
image: the cover image name
"""
if 'page_size' in request.args:
try:
select_from, where, limit = film_sql(
auth=current_user.is_authenticated)
# print(select_from,where,limit )
db = get_db(current_app)
result = dict()
with db.cursor() as c:
if GENRE.issuperset(set(request.args.getlist(
"genre[]"))) and request.args.getlist("genre[]"):
genre = request.args.getlist("genre[]")
for gen in genre:
tmp_where = where + f" movies.genre like '%{escape_string(gen)}%' "
if current_user.is_authenticated:
print(select_from %
(current_user.get_id(), tmp_where, limit))
c.execute(
select_from %
(current_user.get_id(), tmp_where, limit))
else:
# print(select_from%("",tmp_where,limit))
c.execute(select_from % (tmp_where, limit))
tmp = c.fetchall()
tmp = format_result(tmp)
result[gen] = tmp
else:
where = where[:-4]
if current_user.is_authenticated:
# print (" is auth")
# print(select_from%(current_user.get_id(),where,limit))
c.execute(select_from %
(current_user.get_id(), where, limit))
else:
# print(select_from%(where,limit))
c.execute(select_from % (where, limit))
tmp = c.fetchall()
tmp = format_result(tmp)
result["All"] = tmp
return jsonify(result)
except MySQLError as err:
print(err)
abort(400)
def get_film():
"""
Parameters
----------
@movies_id: which movie
-------
@filmfinder_score: score which calculate by our algorithm,
if user has logined in, tailored results will be returned.
imdb_title_id,
@title`,
@original_title`,
@date_published`,
@genre`,
@duration`,
@country`,
@language`,
@director`,
@writer`,
@production_company`,
@actors`,
@description`,
@avg_vote`,
@votes`,
@budget`,
@usa_gross_income`,
@worlwide_gross_income`,
@metascore`,
@reviews_from_users`,
@reviews_from_critics`,
@year`,
image: the cover image name
"""
try:
if 'movie_id' in request.args:
db = get_db(current_app)
with db.cursor() as c:
if current_user.is_authenticated:
sql = f"""SELECT cust_rank('{current_user.get_id()}',m.imdb_title_id) as filmfinder_score,
m.imdb_title_id,
m.`title`,
m.`original_title`,
m.`date_published`,
m.`genre`,
m.`duration`,
m.`country`,
m.`language`,
m.`director`,
m.`writer`,
m.`production_company`,
m.`actors`,
m.`description`,
m.`avg_vote`,
m.`votes`,
m.`budget`,
m.`usa_gross_income`,
m.`worlwide_gross_income`,
m.`metascore`,
m.`reviews_from_users`,
m.`reviews_from_critics`,
m.`year`,
m.`image`
from filmfinder.movies as m
where m.imdb_title_id = %s
"""
else:
sql = "SELECT movies.* from filmfinder.movies where imdb_title_id = %s"
print(sql % (request.args['movie_id']))
c.execute(sql, (request.args['movie_id']))
tmp = c.fetchone()
tmp['filmfinder_score'] = float(tmp['filmfinder_score'])
tmp['date_published'] = tmp['date_published'].isoformat()
tmp['year'] = tmp['year'].isoformat()
return jsonify(tmp)
except MySQLError as err:
print(err)
abort(400)
def add_comment():
"""
Description
-----------
this function add comment which comes with rating.
And it requires login in.
each user can only rate a movie once
Parameter
---------
@movie_id: which movie mush not be empty
@rating: the rating of the movie must not be empty
@comment: comment mush not be empty
Returns
-------
"Ok", 200
"bad request", 400
"""
if {'movie_id','rating','comment'} == set(request.form.keys()):
for k in {'movie_id','rating','comment'}:
if not request.form[k]:
abort(400)
rating_values = ""
rating_values += f"'{escape_string(current_user.get_id())}',"
rating_values += f"{escape_string(request.form['movie_id'])},"
if request.form['rating']:
rating_values += f"{escape_string(request.form['rating'])}"
else:
rating_values += "NULL"
comment_values = ""
comment_values += f"'{escape_string(current_user.get_id())}',"
# comment_values += f"{escape_string(request.form['movie_id'])},"
if request.form['comment']:
comment_values += f"'{escape_string(request.form['comment'])}'"
else:
comment_values += "''"
try:
db = get_db(current_app)
with db.cursor() as c:
#insert comment
sql = " INSERT INTO `filmfinder`.`userXcomment`(user_id,comment) VALUES (%s);"
print(sql%(comment_values))
c.execute(sql%(comment_values))
last_id = c.lastrowid
sql = f"""update `filmfinder`.`userXcomment`
set ancestor_comment_id = {last_id}
where comment_id = {last_id};
"""
c.execute(sql)
#insert rating
sql = f" INSERT INTO `filmfinder`.`userXrating`(user_id,movie_id,rating,comment_id) VALUES (%s,{last_id});"
print(sql%(rating_values))
c.execute(sql%(rating_values))
#update coresponding filmfinder_score for movies table
sql = f""" UPDATE `filmfinder`.`movies`
set filmfinder_score = cust_rank('',movies.imdb_title_id)
where movies.imdb_title_id = {escape_string(request.form['movie_id'])};"""
print(sql)
c.execute(sql)
except MySQLError as err:
db.rollback()
print(err)
else:
db.commit()
return f"OK",200
abort(400)
def get_comments():
"""
Description
-----------
this function returns all comments for given movies
if requesting user has logined in, blocked users' commnets will not return
Parameter
---------
@movie_id: which movie. It must not be empty.
@page: the offset of rows. It must not be empty.
@page_size: how many rows should be return. It must not be empty.
Returns
-------
@rating_commnet:
@user_id: who leavs the comment
@rating: each user only has one comment which includes rating
@comment_id:
@commnet: the body of the commnet
"""
print("is_authenticated",current_user.is_authenticated)
ks = {'movie_id',"page","page_size"}
if {'movie_id',"page","page_size"} == set(request.args.keys()):
for k in {'movie_id',"page","page_size"} :
if not request.args[k]:
abort(400)
try:
sql = """
select
userXrating.user_id,
userXrating.rating,
userXcomment.comment_id,
userXcomment.comment,
users.nick_name
from filmfinder.userXrating
left join filmfinder.userXcomment
on userXrating.comment_id = userXcomment.comment_id
left join filmfinder.users
on userXcomment.user_id = users.user_id
%s -- where
%s -- limit
"""
where = "Where "
# if "user_id" in request.args and request.args['user_id']:
# where += f"user_id = '{escape_string(request.args['user_id'])}' And"
if request.args['movie_id']:
where += f"userXrating.movie_id = {escape_string(request.args['movie_id'])} And"
if current_user.is_authenticated:
sub_sql = f"select blocklisted_user from `filmfinder`.`blocklist` where user_id = '{current_user.get_id()}'"
where += f" userXrating.user_id not in ({sub_sql}) And"
size = int(escape_string(request.args['page_size']))
if size >50:
size = 50
limit = f"limit {size}"
if "page" in request.args:
offset = (int(escape_string(request.args['page'])) -1 )* size
limit += f" OFFSET {offset}"
where = where[:-4]
db = get_db(current_app)
with db.cursor() as c:
# get all comment
# print(sql%(where,limit))
c.execute(sql%(where,limit))
comments = c.fetchall()
# get all response
responses = dict()
for com in comments:
response_sql = """select userXcomment.*,commentXresponse.response_to,users.nick_name
from filmfinder.userXcomment
left join filmfinder.commentXresponse
on userXcomment.comment_id = commentXresponse.comment_id
left join filmfinder.users
on userXcomment.user_id = users.user_id
where ancestor_comment_id = %s
and userXcomment.comment_id != %s """
if current_user.is_authenticated:
response_sql += f"and userXcomment.user_id not in (select blocklisted_user from `filmfinder`.`blocklist` where user_id = '{current_user.get_id()}')"
print(response_sql)
c.execute(response_sql,(com['comment_id'],com['comment_id']))
responses[com['comment_id']] = c.fetchall()
return jsonify({'comments':comments,"responses":responses})
except MySQLError as err:
db.rollback()
print(err)
else:
return f"OK",200
abort(400)