def search_movie(search_criteria):
data = json.loads(search_criteria)
search = data["search"]
myDb = Database.dbConnection()
print(myDb)
sqlString = "SELECT * FROM titles WHERE primaryTitle LIKE %s" + "%'" + "AND titleType = 'movie'"
if "genre:" in search:
search = search.replace(" ", "")
genre = search.rsplit(":")[-1]
sqlString = "SELECT * FROM titles WHERE genres LIKE \'{}\' and titleType = \'movie\' limit 100".format(
genre)
elif "year:" in search:
search = search.replace(" ", "")
year = search.rsplit(":")[-1]
sqlString = "SELECT * FROM titles WHERE startYear LIKE \'{}\' and titleType = \'movie\' limit 100".format(
year)
value = search
result = Database.selectStatement(myDb, sqlString, value)
fetch = result.fetchall()
info = {}
for row in fetch:
info[row[0]] = {
"movieID": row[0],
"title": row[3],
"isAdult": row[4],
"startYear": row[5],
"runtime": row[7],
"genres": row[8]
}
return json.dumps(info)
def get_subscribers():
myDb = Database.dbConnection()
print(myDb)
sqlString = "SELECT email FROM users WHERE isSubscribed = '1'"
result = Database.execStatement(myDb, sqlString)
fetch = result.fetchall()
emailList = []
for i in fetch:
emailList.append(i[0])
return emailList
def update_name(update_info):
data = json.loads(update_info)
name = data["name"]
userid = str(data["userid"])
myDb = Database.dbConnection()
print(myDb)
sqlString = "UPDATE users SET fullname = %s WHERE userid = %s "
values = (name, userid)
result = Database.execStatement(myDb, sqlString, values)
myDb.commit()
def update_subscription(update_info):
data = json.loads(update_info)
status = str(data["status"])
userid = str(data["userid"])
myDb = Database.dbConnection()
print(myDb)
sqlString = "UPDATE users SET isSubscribed = %s WHERE userid = %s"
values = (status, userid)
result = Database.execStatement(myDb, sqlString, values)
myDb.commit()
def update_ticket(info):
data = json.loads(info)
ticket_id = data["ticketID"]
myDb = Database.dbConnection()
print(myDb)
sqlString = "UPDATE tickets SET status = 'Resolved' WHERE ticket_id = %s"
value = ticket_id
result = Database.execStatement(myDb, sqlString), value
myDb.commit()
def create_ticket(info):
data = json.loads(info)
user = data["user"]
ticket_id = data["ticket_id"]
myDb = Database.dbConnection()
print(myDb)
sqlString = "INSERT INTO tickets (ticket_id, user, status) VALUES (%s, %s, %s)"
values = (ticket_id, user, 'Unresolved')
result = Database.execStatement(myDb, sqlString, values)
myDb.commit()
def change_password(info):
data = json.loads(info)
current_hash = data["current_hash"]
change_hash = data["change_hash"]
userid = str(data["userid"])
myDb = Database.dbConnection()
print(myDb)
sqlString = "UPDATE users SET passwordHash = %s WHERE userid = %s AND passwordHash = %s"
values = (current_hash, userid, change_hash)
result = Database.execStatement(myDb, sqlString, values)
myDb.commit()
def create_user(user_info):
data = json.loads(user_info)
email = data["email"]
userName = data["userName"]
passwordHash = data["passwordHash"]
fullName = data["fullName"]
myDb = Database.dbConnection()
print(myDb)
sqlString = "INSERT INTO users (email, userName, passwordHash, fullName) VALUES (%s, %s, %s, %s)"
values = (email, userName, passwordHash, fullName)
result = Database.execStatement(myDb, sqlString, values)
myDb.commit()
def get_names(name_id):
data = json.loads(name_id)
search = data["nameID"]
myDb = Database.dbConnection()
print(myDb)
sqlString = "SELECT primaryName FROM name_basic WHERE nconst = %s"
value = search
result = Database.execStatement(myDb, sqlString, value)
fetch = result.fetchone()
info = {"name": fetch}
return json.dumps(info)
def get_movieID(movie_title):
data = json.loads(movie_title)
search = data["movieTitle"]
myDb = Database.dbConnection()
print(myDb)
sqlString = "SELECT tconst FROM titles WHERE primaryTitle = %s AND titleType = 'movie'"
value = search
result = Database.execStatement(myDb, sqlString, value)
fetch = result.fetchone()[0]
info = {"titleID": fetch}
return json.dumps(info)
def get_tickets():
myDb = Database.dbConnection()
print(myDb)
sqlString = "SELECT * FROM tickets"
result = Database.selectStatement(myDb, sqlString)
fetch = result.fetchall()
info = {}
for ticket in fetch:
info[ticket[0]] = {
"ticket_id": ticket[0],
"user": ticket[1],
"status": ticket[2]
}
return json.dumps(info)
def get_user(login_info):
data = json.loads(login_info)
email = data["email"]
passwordHash = data["passwordhash"]
myDb = Database.dbConnection()
print(myDb)
sqlString = "SELECT passwordHash, userName FROM users WHERE email = " + "'" + email + "'"
result = Database.selectStatement(myDb, sqlString)
if (result.rowcount == 1):
fetch = result.fetchone()
if (fetch[0] == passwordHash):
return fetch[1]
else:
return False
def check_user(user_info):
data = json.loads(user_info)
email = data["email"]
userName = data["userName"]
myDb = Database.dbConnection()
print(myDb)
sqlString_Email = "SELECT email FROM users WHERE email = " + "'" + email + "'"
sqlString_UserName = "******" + "'" + userName + "'"
result1 = Database.selectStatement(myDb, sqlString_Email)
result2 = Database.selectStatement(myDb, sqlString_UserName)
if (result1.rowcount != 0):
return 'email taken'
elif (result2.rowcount != 0):
return 'username taken'
else:
return 'clear'
def get_cast(movie_id):
data = json.loads(movie_id)
search = data["titleID"]
myDb = Database.dbConnection()
print(myDb)
sqlString = "Select a.nconst, a.primaryName, b.category, b.job, b.characters FROM 9uAko4qR3y.name_basic a, 9uAko4qR3y.cast b WHERE a.nconst = b.nconst AND b.tconst = %s"
value = search
result = Database.execStatement(myDb, sqlString, value)
cast_fetch = result.fetchall()
completeInfo = {}
for row in cast_fetch:
completeInfo[row[0]] = {
"name": row[1],
"category": row[2],
"job": row[3],
"characters": row[4]
}
return json.dumps(completeInfo)
def get_user_info(info):
data = json.loads(info)
userid = str(data["userid"])
myDb = Database.dbConnection()
print(myDb)
sqlString = "SELECT username, fullname, isSubscribed FROM users WHERE userid = %s"
value = userid
result = Database.execStatement(myDb, sqlString, value)
fetch = result.fetchall()
if fetch != None:
user_info = {
"status": 1,
"username": fetch[0][0],
"fullname": fetch[0][1],
"isSubscribed": fetch[0][2]
}
else:
user_info = {"status": 0}
return json.dumps(user_info)
def search_movie(search_criteria):
data = json.loads(search_criteria)
search = data["search"]
myDb = Database.dbConnection()
print(myDb)
sqlString = "SELECT * FROM titles WHERE primaryTitle LIKE " + "'" + search + "%'" + "AND titleType = 'movie'"
result = Database.selectStatement(myDb, sqlString)
fetch = result.fetchall()
info = {}
for row in fetch:
info[row[0]] = {
"movieID": row[0],
"title": row[3],
"isAdult": row[4],
"startYear": row[5],
"runtime": row[7],
"genres": row[8]
}
return json.dumps(info)
def make_shell_context():
return {'db': Database.dbConnection()}