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 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 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 = %s" value_email = email sqlString_UserName = "******" value_username = userName result1 = Database.execStatement(myDb, sqlString_Email, value_email) result2 = Database.execStatement(myDb, sqlString_UserName, value_username) if (result1.rowcount != 0): return 'email taken' elif (result2.rowcount != 0): return 'username taken' else: return 'clear'
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 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_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 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_user(login_info): data = json.loads(login_info) email = data["email"] passwordHash = data["passwordhash"] myDb = Database.dbConnection() print(myDb) sqlString = "SELECT passwordHash, userid FROM users WHERE email = %s" value_email = email result = Database.execStatement(myDb, sqlString) if (result.rowcount == 1): fetch = result.fetchone() if (fetch[0] == passwordHash): return fetch[1] else: return False
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 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)