def createUser(): logging.info("================USER CREATION") try: email = request.json["email"] except: return json.dumps({"code": 2, "response": error_messages[2]}) try: name = request.json["name"] except: name = None logging.info("NAME IS None") logging.info("_____________________") try: username = request.json["username"] except: username = None logging.info("USERNAME IS None") logging.info("_____________________") try: about = request.json["about"] except: about = None logging.info("ABOUT IS None") logging.info("_____________________") if (not isString([name, email, username, about])): logging.info("one or more params not string") return json.dumps({"code": 2, "response": error_messages[2]}) sql = "SELECT idUser FROM User WHERE email = %s" cursor.execute(sql, [email]) if (cursor.fetchone() != None): logging.info(email + " is already exists") return json.dumps({"code": 5, "response": error_messages[5]}) isAnonymous = False if ("isAnonymous" in request.json): isAnonymous = request.json["isAnonymous"] sql = "INSERT INTO User(username, email, name, about, isAnonymous) VALUES (%s, %s, %s, %s, %s)" cursor.execute(sql, [username, email, name, about, isAnonymous]) sql = "SELECT MAX(idUser) FROM User" cursor.execute(sql) idU = cursor.fetchone()[0] data = {} data['about'] = about data['email'] = email data['id'] = idU data['isAnonymous'] = isAnonymous data['name'] = name data['username'] = username answer = {"code": 0, "response": data} response = json.dumps(answer) logging.info("================SUCCESSFUL USER CREATION\n") return response
def status(): db_info = { "forum": 0, "user": 0, "thread": 0, "post": 0 } cursor.execute("SELECT COUNT(*) FROM Forum") db_info["forum"] = cursor.fetchone()[0] cursor.execute("SELECT COUNT(*) FROM User ") db_info["user"] = cursor.fetchone()[0] cursor.execute("SELECT COUNT(*) FROM Thread") db_info["thread"] = cursor.fetchone()[0] cursor.execute("SELECT COUNT(*) FROM Post") db_info["post"] = cursor.fetchone()[0] response = json.dumps({"code": 0, "response": db_info}) return response
def closeThread(): tic = time() # logging.info("=====================================CLOSING THREAD BEGIN==========================================") try: thread = request.json["thread"] except: return json.dumps({"code": 2, "response": error_messages[2]}) # logging.info(" thread : " + str(thread)) sql = "SELECT idThread FROM Thread WHERE idThread = %s" cursor.execute(sql, [thread]) data = cursor.fetchone() if (not data): # logging.info("=====================================CLOSING THREAD END WITHOUT DATA===============================\n") return json.dumps({"code": 1, "response": error_messages[1]}) sql = "UPDATE Thread SET isClosed = 1 WHERE idThread = %s" cursor.execute(sql, [thread]) response = json.dumps({"code": 0, "response": thread}) # logging.info(" Response : ") # logging.info(response) # logging.info("=====================================CLOSING THREAD END============================================\n") tac =time() MyTime = tac - tic if MyTime > LimitTime: print (MyTime, "/db/api/thread/close/ +++POST") return response
def getArrayThreadsFromDDictionary(dictionary, related): array = [] for item in dictionary: try: threadID = item[0] sql = "SELECT count(*) FROM Post WHERE idThread = %s AND isDeleted = 0" cursor.execute(sql, [threadID]) count_posts = cursor.fetchone()[0] logging.info(" Count posts of thread " + str(threadID) + " is " + str(count_posts)) except: count_posts = 0 answer = {} answer["id"] = item[0] answer["title"] = item[1] answer["message"] = item[2] answer["slug"] = item[3] answer["date"] = str(item[4]) answer["isClosed"] = item[5] answer["isDeleted"] = item[6] answer["forum"] = getForumShortNameById(item[7]) answer["user"] = getUserEmailByID(item[8]) answer["likes"] = item[9] answer["dislikes"] = item[10] answer["posts"] = count_posts answer["points"] = answer["likes"] - answer["dislikes"] if "user" in related: answer["user"] = getUserInfoByEmail(answer["user"]) if "forum" in related: answer["forum"] = getForumDetailsByShortName(answer["forum"]) array.append(answer) return array
def restoreThread(): tic = time() from Post import restorePostsOfThread if "thread" in request.json: # logging.info("RESTORING THREAD") thread = request.json["thread"] else: return json.dumps({"code": 2, "response": error_messages[2]}) sql = "SELECT idThread FROM Thread WHERE idThread = %s" cursor.execute(sql, [thread]) if cursor.fetchone() is None: return json.dumps({"code": 1, "response": error_messages[1]}) restorePostsOfThread(thread) sql = "UPDATE Thread SET isDeleted = 0 WHERE idThread = %s" cursor.execute(sql, [thread]) response = json.dumps({ "code": 0, "response": {"thread": thread}}) # logging.info("REMOVING THREAD SUCCESSFULL\n") tac =time() MyTime = tac - tic if MyTime > LimitTime: print (MyTime, "/db/api/thread/restore/ +++POST") return response
def updatePost(): tic = time() #logging.info(" Updating post") if "post" in request.json and "message" in request.json: post = request.json["post"] message = request.json["message"] else: return json.dumps({"code": 2, "response": error_messages[2]}) sql = "SELECT idPost FROM Post WHERE idPost = %s" cursor.execute(sql, [post]) result_arr = cursor.fetchone() result = result_arr[0] if not result: return json.dumps({"code": 1, "response": error_messages[1]}) sql = "UPDATE Post SET message = %s WHERE idPost = %s" cursor.execute(sql, [message, post]) response = json.dumps({"code": 0, "response": post}) # logging.info(" Post " + str(post) + (" is updated successfully\n")) tac =time() MyTime = tac - tic if MyTime > LimitTime: print (MyTime, "/db/api/post/update/ +++POST") return response
def getUserIdByEmail(email): logging.info(" getting user by email : ") sql = "SELECT idUser FROM User WHERE email = %s" cursor.execute(sql, [email]) q_result = cursor.fetchone()[0] logging.info(" getted user by email : " + str(q_result)) return q_result
def getForumIdByShortName(short_name): sql = "SELECT idForum FROM Forum WHERE short_name = %s" cursor.execute(sql, [short_name]) data = cursor.fetchone() if (not data): return None answer = data[0] return answer
def getForumShortNameById(id): sql = "SELECT short_name FROM Forum WHERE idForum = %s" cursor.execute(sql, [id]) data = cursor.fetchone() if (not data): return None answer = data[0] return answer
def status(): tic = time() db_info = { "forum": 0, "user": 0, "thread": 0, "post": 0 } cursor.execute("SELECT COUNT(*) FROM Forum") db_info["forum"] = cursor.fetchone()[0] cursor.execute("SELECT COUNT(*) FROM User ") db_info["user"] = cursor.fetchone()[0] cursor.execute("SELECT COUNT(*) FROM Thread") db_info["thread"] = cursor.fetchone()[0] cursor.execute("SELECT COUNT(*) FROM Post") db_info["post"] = cursor.fetchone()[0] response = json.dumps({"code": 0, "response": db_info}) tac =time() MyTime = tac - tic if MyTime > LimitTime: print (MyTime, "/db/api/status/") return response
def createForum(): tic = time() #logging.info("================FORUM CREATION") # logging.info("REQUEST :") # logging.info(request.json) # logging.info("SH_NAME : " + request.json["short_name"]) # logging.info("USER : "******"user"]) # logging.info("NAME : " + request.json["name"].encode("UTF-8")) try: name = request.json["name"].encode("UTF-8") #logging.info("NAME : " + name) short_name = request.json["short_name"] #logging.info("SHORT_NAME : " + short_name) user = request.json["user"] #logging.info("USER : "******"error in parsing params") return json.dumps({"code": 2, "response": error_messages[2]}) cursor.execute("SELECT idUser FROM User WHERE User.email = %s", [user]) id_User = cursor.fetchone() if (not id_User): return json.dumps({"code": 1, "response": error_messages[1]}) id_User = id_User[0] sql = "SELECT * FROM Forum WHERE name = %s" cursor.execute(sql, [name]) if (cursor.fetchone()): return json.dumps({"code": 5, "response": error_messages[5]}) sql = "INSERT INTO Forum (name, short_name, idFounder) VALUES (%s, %s, %s)" cursor.execute(sql, [name, short_name, id_User]) sql = "SELECT max(idForum) FROM Forum" cursor.execute(sql) idF = cursor.fetchone()[0] answer = {"code": 0, "response": {"id": idF, "name": name, "short_name":short_name, "user": user}} response = json.dumps(answer) #logging.info("================SUCCESSFUL FORUM CREATION\n") tac = time() MyTime = tac - tic if MyTime > LimitTime: print (MyTime, "db/api/forum/create/") return response
def getUserEmailByID(id): sql = "SELECT email FROM User WHERE idUser = %s" cursor.execute(sql, [id]) q_result = cursor.fetchone() if (q_result != None): return q_result[0] else: return None
def get(user_id): if not user_id: return None cursor.execute("SELECT * FROM users WHERE id = " + str(user_id)) res = cursor.fetchone() if res: user = User(res) return user else: return None
def createThread(): tic = time() #logging.info("================Thread CREATION") try: forum = request.json["forum"] title = request.json["title"] isClosed = request.json["isClosed"] date = request.json["date"] message = request.json["message"] slug = request.json["slug"] user = request.json["user"] except: return json.dumps({"code": 2, "response": error_messages[2]}) try: isDeleted = request.json["isDeleted"] except: isDeleted = False try: id_Forum = getForumDetailsByShortName(forum)["id"] id_User = getUserInfoByEmail(user)["id"] except: return json.dumps({"code": 2, "response": error_messages[2]}) sql = "INSERT INTO Thread(title, message, slug, date, isClosed, isDeleted, idForum, idAuthor, likes, dislikes) " \ "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" cursor.execute(sql, [title, message, slug, date, isClosed, isDeleted, id_Forum, id_User, 0 , 0]) sql = "SELECT MAX(idThread) FROM Thread" cursor.execute(sql) idTh = cursor.fetchone()[0] answer = {} answer["date"] = date answer["forum"] = forum answer["id"] = idTh answer["isClosed"] = isClosed answer["isDeleted"] = isDeleted answer["likes"] = 0 answer["dislikes"] = 0 answer["message"] = message answer["points"] = answer["likes"] - answer["dislikes"] answer["posts"] = 0 answer["slug"] = slug answer["title"] = title answer["user"] = user response = json.dumps({"code": 0, "response": answer }) # logging.info(" Answer : " + response) #logging.info("================SUCCESSFUL THREAD CREATION\n") tac =time() MyTime = tac - tic if MyTime > LimitTime: print (MyTime, "/db/api/thread/create/ +++POST") return response
def getForumDetailsById(id): sql = "SELECT * FROM Forum WHERE idForum = %s" cursor.execute(sql, [id]) data = cursor.fetchone() if (not data): return None answer = {} answer["id"] = data[0] answer["name"] = data[1] answer["short_name"] = data[2] answer["idFounder"] = data[3] return answer
def createThread(): logging.info("================Thread CREATION") try: forum = request.json["forum"] title = request.json["title"] isClosed = request.json["isClosed"] date = request.json["date"] message = request.json["message"] slug = request.json["slug"] user = request.json["user"] except: return json.dumps({"code": 2, "response": error_messages[2]}) try: isDeleted = request.json["isDeleted"] except: isDeleted = False try: id_Forum = getForumDetailsByShortName(forum)["id"] id_User = getUserInfoByEmail(user)["id"] except: return json.dumps({"code": 2, "response": error_messages[2]}) sql = "INSERT INTO Thread(title, message, slug, date, isClosed, isDeleted, idForum, idAuthor, likes, dislikes) " \ "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" cursor.execute(sql, [ title, message, slug, date, isClosed, isDeleted, id_Forum, id_User, 0, 0 ]) sql = "SELECT MAX(idThread) FROM Thread" cursor.execute(sql) idTh = cursor.fetchone()[0] answer = {} answer["date"] = date answer["forum"] = forum answer["id"] = idTh answer["isClosed"] = isClosed answer["isDeleted"] = isDeleted answer["likes"] = 0 answer["dislikes"] = 0 answer["message"] = message answer["points"] = answer["likes"] - answer["dislikes"] answer["posts"] = 0 answer["slug"] = slug answer["title"] = title answer["user"] = user response = json.dumps({"code": 0, "response": answer}) logging.info(" Answer : " + response) logging.info("================SUCCESSFUL THREAD CREATION\n") return response
def createForum(): logging.info("================FORUM CREATION") # logging.info("REQUEST :") # logging.info(request.json) # logging.info("SH_NAME : " + request.json["short_name"]) # logging.info("USER : "******"user"]) # logging.info("NAME : " + request.json["name"].encode("UTF-8")) try: name = request.json["name"].encode("UTF-8") logging.info("NAME : " + name) short_name = request.json["short_name"] logging.info("SHORT_NAME : " + short_name) user = request.json["user"] logging.info("USER : "******"error in parsing params") return json.dumps({"code": 2, "response": error_messages[2]}) cursor.execute("SELECT idUser FROM User WHERE User.email = %s", [user]) id_User = cursor.fetchone() if (not id_User): return json.dumps({"code": 1, "response": error_messages[1]}) id_User = id_User[0] sql = "SELECT * FROM Forum WHERE name = %s" cursor.execute(sql, [name]) if (cursor.fetchone()): return json.dumps({"code": 5, "response": error_messages[5]}) sql = "INSERT INTO Forum (name, short_name, idFounder) VALUES (%s, %s, %s)" cursor.execute(sql, [name, short_name, id_User]) sql = "SELECT max(idForum) FROM Forum" cursor.execute(sql) idF = cursor.fetchone()[0] answer = {"code": 0, "response": {"id": idF, "name": name, "short_name":short_name, "user": user}} response = json.dumps(answer) logging.info("================SUCCESSFUL FORUM CREATION\n") return response
def map2(): if request.method == "POST": req = request.form scoords = req['scoords'] ecoords = req['ecoords'] cursor.execute( """SELECT * FROM `users` WHERE `email` LIKE '{}' """.format( email)) user = cursor.fetchone() pooling = 1 cursor.execute( """UPDATE `users` set pooling='{}' WHERE email='{}' """.format( pooling, email)) cursor.execute( """UPDATE `users` set starting_coords='{}' WHERE email='{}' """. format(scoords, email)) cursor.execute( """UPDATE `users` set ending_coords='{}' WHERE email='{}' """. format(ecoords, email)) conn.commit() cursor.execute( """SELECT * FROM `users` WHERE `email` LIKE '{}' """.format(email)) user = cursor.fetchone() cursor.execute("""SELECT * FROM `users` """) records = cursor.fetchall() u1 = (user[1], user[7]) u2 = (user[1], user[8]) d1 = {record[1]: record[7] for record in records} d2 = {record[1]: record[8] for record in records} # User's match based on compatibility types max_user = matching.match(u2, d2) cursor.execute( """SELECT * FROM `users` WHERE `name` LIKE '{}' """.format(max_user)) usr_phn = cursor.fetchone() phn = usr_phn[4] pooling = 0 cursor.execute( """UPDATE `users` set pooling='{}' WHERE email='{}' """.format( pooling, email)) return render_template('map2.html', user=max_user, phn=phn)
def getThreadDetailsByID(threadID, related): sql = "SELECT * FROM Thread WHERE idThread = %s" cursor.execute(sql, [threadID]) data = cursor.fetchone() if (not data): # logging.info("Thread not found") return None try: sql = "SELECT count(*) FROM Post WHERE idThread = %s AND isDeleted = 0" cursor.execute(sql, [threadID]) count_posts = cursor.fetchone()[0] # logging.info(" Count posts of thread " + str(threadID) + " is " + str(count_posts)) except: count_posts = 0 answer = {} answer["id"] = data[0] answer["title"] = data[1] answer["message"] = data[2] answer["slug"] = data[3] answer["date"] = str(data[4]) answer["isClosed"] = data[5] answer["isDeleted"] = data[6] forum_details = getForumDetailsById(data[7]) answer["forum"] = forum_details["short_name"] answer["user"] = getUserEmailByID(data[8]) answer["likes"] = data[9] answer["dislikes"] = data[10] answer["posts"] = count_posts answer["points"] = answer["likes"] - answer["dislikes"] if "user" in related: data_user = getUserInfoByEmail(answer["user"]) answer["user"] = data_user if "forum" in related: data_forum = getForumDetailsByShortName(answer["forum"]) answer["forum"] = data_forum # logging.info(" ===========Answer getThreadByID() : ") #logging.info(answer) # logging.info(" ===================================\n") return answer
def getThreadDetailsByID(threadID, related): sql = "SELECT * FROM Thread WHERE idThread = %s" cursor.execute(sql, [threadID]) data = cursor.fetchone() if (not data): logging.info("Thread not found") return None try: sql = "SELECT count(*) FROM Post WHERE idThread = %s AND isDeleted = 0" cursor.execute(sql, [threadID]) count_posts = cursor.fetchone()[0] logging.info(" Count posts of thread " + str(threadID) + " is " + str(count_posts)) except: count_posts = 0 answer = {} answer["id"] = data[0] answer["title"] = data[1] answer["message"] = data[2] answer["slug"] = data[3] answer["date"] = str(data[4]) answer["isClosed"] = data[5] answer["isDeleted"] = data[6] forum_details = getForumDetailsById(data[7]) answer["forum"] = forum_details["short_name"] answer["user"] = getUserEmailByID(data[8]) answer["likes"] = data[9] answer["dislikes"] = data[10] answer["posts"] = count_posts answer["points"] = answer["likes"] - answer["dislikes"] if "user" in related: data_user = getUserInfoByEmail(answer["user"]) answer["user"] = data_user if "forum" in related: data_forum = getForumDetailsByShortName(answer["forum"]) answer["forum"] = data_forum logging.info(" ===========Answer getThreadByID() : ") logging.info(answer) logging.info(" ===================================\n") return answer
def getForumDetailsByShortName(short_name): from User import getUserEmailByID sql = "SELECT * FROM Forum WHERE short_name = %s" cursor.execute(sql, [short_name]) data = cursor.fetchone() if (not data): return None answer = {} answer["id"] = data[0] answer["name"] = data[1] answer["short_name"] = data[2] answer["idFounder"] = data[3] answer["user"] = getUserEmailByID(answer["idFounder"]) return answer
def edit_product(data): sql = "SELECT * FROM products WHERE product_id = %d" % data["product_id"] print(sql) status = 0 res = '' try: cursor.execute(sql) result = cursor.fetchone() print(type(result[6])) if result[6] == 2 or result[6] == 3 or result[6] == 6 or result[ 6] == 7 or result[6] == 15: sql = "UPDATE products SET product_name = '%s',product_description = '%s',product_price = '%8.2f', product_qty = '%d', created_by = '%d', actions = '%d' WHERE product_id = %d" % ( data["product_name"], data["product_description"], data["product_price"], data["product_qty"], data["created_by"], data["actions"], data["product_id"]) print(sql) status = 0 res = '' try: cursor.execute(sql) db.commit() status = 200 res = 'Product updated successfully' except pymysql.Error as e: try: print("MySQL Error" + str(e)) return None except IndexError: print("MySQL Error: %s") % str(e) return None except: db.rollback() status = 401 res = 'Something went wrong. Try again' else: status = 201 res = "Not authorized" except: print("Error: unable to fetch data") return res, status
def removePost(): if "post" in request.json: post = request.json["post"] else: return json.dumps({"code": 2, "response": error_messages[2]}) sql = "SELECT idPost FROM Post WHERE idPost = %s" cursor.execute(sql, [post]) result = cursor.fetchone() if not result: return json.dumps({"code": 1, "response": error_messages[1]}) sql = "UPDATE Post SET isDeleted = 1 WHERE idPost = %s" cursor.execute(sql, [post]) response = json.dumps({"code": 0, "response": post}) return response
def check(): conn = mysql.connect() cursor = conn.cursor() print "ddd" username = str(request.form["name"]) userpassword = str(request.form["password"]) cursor.execute("SELECT * FROM usertable WHERE username ='******' and userpassword = '******'") username = cursor.fetchone() id = username[0] name = username[1] password = username[2] age = username[3] gender = username[4] permission = username[5] status = username[6] print(id) print(name) print(password) print(age) print(gender) print(permission) print(status) #print(username) if not username: return render_template("index.html") elif username and permission == "admin": return render_template("adminpage.html") elif username and permission == "user": return render_template("index.html") elif username and permission == "manager": return render_template("adminpage.html") cursor.execute('call add_loginvisitor(%s,%s)', (username, userpassword))
def form_data(): global result if request.method == "POST": req = request.form movie = req['movie'] music = req['music'] sport = req['sport'] choice = req['choice'] result = movie + music + sport + choice cursor.execute( """SELECT * FROM `users` WHERE `email` LIKE '{}' AND `password` LIKE '{}' """ .format(email, password)) user = cursor.fetchone() cursor.execute( """UPDATE `users` set results='{}' WHERE email='{}' """.format( result, email)) conn.commit() conn.commit() return render_template('home.html')
def openThread(): if "thread" in request.json: thread = request.json["thread"] else: return json.dumps({"code": 2, "response": error_messages[2]}) logging.info(" thread : " + str(thread)) sql = "SELECT idThread FROM Thread WHERE idThread = %s" cursor.execute(sql, [thread]) data = cursor.fetchone() if not data: logging.info("=====================================CLOSING THREAD END WITHOUT DATA===============================\n") return json.dumps({"code": 1, "response": error_messages[1]}) sql = "UPDATE Thread SET isClosed = 0 WHERE idThread = %s" cursor.execute(sql, [thread]) response = json.dumps({"code": 0, "response": thread}) return response
def getUserInfoByID(id): sql = "SELECT * FROM User WHERE idUser = %s" cursor.execute(sql, [id]) q_result = cursor.fetchone() if (q_result != None): data = {} data["id"] = q_result[0] data["username"] = q_result[1] data["email"] = q_result[2] data["name"] = q_result[3] data["about"] = q_result[4] data["isAnonymous"] = q_result[5] data["followers"] = getFollowerEmails(data["id"], None, None, None) data["following"] = getFollowingEmails(data["id"], None, None, None) data["subscriptions"] = getSubscriptions(data["id"]) return data else: return None
def updatePost(): logging.info(" Updating post") if "post" in request.json and "message" in request.json: post = request.json["post"] message = request.json["message"] else: return json.dumps({"code": 2, "response": error_messages[2]}) sql = "SELECT idPost FROM Post WHERE idPost = %s" cursor.execute(sql, [post]) result_arr = cursor.fetchone() result = result_arr[0] if not result: return json.dumps({"code": 1, "response": error_messages[1]}) sql = "UPDATE Post SET message = %s WHERE idPost = %s" cursor.execute(sql, [message, post]) response = json.dumps({"code": 0, "response": post}) logging.info(" Post " + str(post) + (" is updated successfully\n")) return response
def openThread(): if "thread" in request.json: thread = request.json["thread"] else: return json.dumps({"code": 2, "response": error_messages[2]}) logging.info(" thread : " + str(thread)) sql = "SELECT idThread FROM Thread WHERE idThread = %s" cursor.execute(sql, [thread]) data = cursor.fetchone() if not data: logging.info( "=====================================CLOSING THREAD END WITHOUT DATA===============================\n" ) return json.dumps({"code": 1, "response": error_messages[1]}) sql = "UPDATE Thread SET isClosed = 0 WHERE idThread = %s" cursor.execute(sql, [thread]) response = json.dumps({"code": 0, "response": thread}) return response
def removePost(): tic = time() if "post" in request.json: post = request.json["post"] else: return json.dumps({"code": 2, "response": error_messages[2]}) sql = "SELECT idPost FROM Post WHERE idPost = %s" cursor.execute(sql, [post]) result = cursor.fetchone() if not result: return json.dumps({"code": 1, "response": error_messages[1]}) sql = "UPDATE Post SET isDeleted = 1 WHERE idPost = %s" cursor.execute(sql, [post]) response = json.dumps({"code": 0, "response": post}) tac =time() MyTime = tac - tic if MyTime > LimitTime: print (MyTime, "/db/api/post/remove/ +++POST") return response
def removePost(): tic = time() if "post" in request.json: post = request.json["post"] else: return json.dumps({"code": 2, "response": error_messages[2]}) sql = "SELECT idPost FROM Post WHERE idPost = %s" cursor.execute(sql, [post]) result = cursor.fetchone() if not result: return json.dumps({"code": 1, "response": error_messages[1]}) sql = "UPDATE Post SET isDeleted = 1 WHERE idPost = %s" cursor.execute(sql, [post]) response = json.dumps({"code": 0, "response": post}) tac = time() MyTime = tac - tic if MyTime > LimitTime: print(MyTime, "/db/api/post/remove/ +++POST") return response
def get_product(product_id): sql = "SELECT * FROM products WHERE product_id = %d" % product_id status = 0 res = '' try: cursor.execute(sql) result = cursor.fetchone() print(type(result)) data = { 'id': result[0], 'product_name': result[1], 'product_description': result[2], 'product_price': result[3], 'product_qty': result[4], 'created_by': result[5], 'actions': result[6] } status = 200 res = data except: print("Error: unable to fetch data") return res, status
def closeThread(): logging.info("=====================================CLOSING THREAD BEGIN==========================================") try: thread = request.json["thread"] except: return json.dumps({"code": 2, "response": error_messages[2]}) logging.info(" thread : " + str(thread)) sql = "SELECT idThread FROM Thread WHERE idThread = %s" cursor.execute(sql, [thread]) data = cursor.fetchone() if (not data): logging.info("=====================================CLOSING THREAD END WITHOUT DATA===============================\n") return json.dumps({"code": 1, "response": error_messages[1]}) sql = "UPDATE Thread SET isClosed = 1 WHERE idThread = %s" cursor.execute(sql, [thread]) response = json.dumps({"code": 0, "response": thread}) logging.info(" Response : ") logging.info(response) logging.info("=====================================CLOSING THREAD END============================================\n") return response
def restoreThread(): from Post import restorePostsOfThread if "thread" in request.json: logging.info("RESTORING THREAD") thread = request.json["thread"] else: return json.dumps({"code": 2, "response": error_messages[2]}) sql = "SELECT idThread FROM Thread WHERE idThread = %s" cursor.execute(sql, [thread]) if cursor.fetchone() is None: return json.dumps({"code": 1, "response": error_messages[1]}) restorePostsOfThread(thread) sql = "UPDATE Thread SET isDeleted = 0 WHERE idThread = %s" cursor.execute(sql, [thread]) response = json.dumps({"code": 0, "response": {"thread": thread}}) logging.info("REMOVING THREAD SUCCESSFULL\n") return response
def restoreThread(): from Post import restorePostsOfThread if "thread" in request.json: logging.info("RESTORING THREAD") thread = request.json["thread"] else: return json.dumps({"code": 2, "response": error_messages[2]}) sql = "SELECT idThread FROM Thread WHERE idThread = %s" cursor.execute(sql, [thread]) if cursor.fetchone() is None: return json.dumps({"code": 1, "response": error_messages[1]}) restorePostsOfThread(thread) sql = "UPDATE Thread SET isDeleted = 0 WHERE idThread = %s" cursor.execute(sql, [thread]) response = json.dumps({ "code": 0, "response": {"thread": thread}}) logging.info("REMOVING THREAD SUCCESSFULL\n") return response
def work(): global users_dic_work if not current_user.is_authenticated: return redirect(url_for('login')) if current_user.weapon_id is None: flash("未装备武器!") return redirect(url_for('index')) else: if users_dic_work[current_user.id] == 1: cursor.execute("SELECT * FROM items WHERE id=%s" % current_user.weapon_id) res = cursor.fetchone() weapon = Item(res) gold = current_user.gold cursor.execute("UPDATE users SET gold = %s WHERE id=%s" % (gold + weapon.value, current_user.id)) conn.commit() flash("获得" + str(weapon.value) + "金币!") users_dic_work[current_user.id] = 0 return redirect(url_for('index')) else: flash("未到出击时间!") return redirect(url_for('index'))
def getPostDetailsByID(postID, related): sql = "SELECT * FROM Post WHERE idPost = %s" cursor.execute(sql, [postID]) data = cursor.fetchone() # logging.info(data) if (not data): #logging.info(" Thread not found") return None answer = {} answer["id"] = data[0] answer["parent"] = data[1] answer["isApproved"] = data[2] answer["isHighlighted"] = data[3] answer["isEdited"] = data[4] answer["isSpam"] = data[5] answer["isDeleted"] = data[6] answer["likes"] = data[7] answer["dislikes"] = data[8] answer["date"] = str(data[9]) answer["message"] = data[10] answer["forum"] = getForumShortNameById(data[11]) answer["thread"] = data[12] answer["user"] = getUserEmailByID(data[13]) answer["points"] = answer["likes"] - answer["dislikes"] if "user" in related: data_user = getUserInfoByEmail(answer["user"]) answer["user"] = data_user if "forum" in related: answer["forum"] = getForumDetailsByShortName(answer["forum"]) if "thread" in related: answer["thread"] = getThreadDetailsByID(answer["thread"], []) # logging.info(" ===========Answer getPostByID() : ") # logging.info(answer) #logging.info(" ===================================") return answer
def getPostDetailsByID(postID, related): sql = "SELECT * FROM Post WHERE idPost = %s" cursor.execute(sql, [postID]) data = cursor.fetchone() logging.info(data) if (not data): logging.info(" Thread not found") return None answer = {} answer["id"] = data[0] answer["parent"] = data[1] answer["isApproved"] = data[2] answer["isHighlighted"] = data[3] answer["isEdited"] = data[4] answer["isSpam"] = data[5] answer["isDeleted"] = data[6] answer["likes"] = data[7] answer["dislikes"] = data[8] answer["date"] = str(data[9]) answer["message"] = data[10] answer["forum"] = getForumShortNameById(data[11]) answer["thread"] = data[12] answer["user"] = getUserEmailByID(data[13]) answer["points"] = answer["likes"] - answer["dislikes"] if "user" in related: data_user = getUserInfoByEmail(answer["user"]) answer["user"] = data_user if "forum" in related: answer["forum"] = getForumDetailsByShortName(answer["forum"]) if "thread" in related: answer["thread"] = getThreadDetailsByID(answer["thread"], []) logging.info(" ===========Answer getPostByID() : ") logging.info(answer) logging.info(" ===================================") return answer
def createUser(): tic = time() # logging.info("================USER CREATION") try: email = request.json["email"] except: return json.dumps({"code": 2, "response": error_messages[2]}) try: name = request.json["name"] except: name = None # logging.info("NAME IS None") # logging.info("_____________________") try: username = request.json["username"] except: username = None # logging.info("USERNAME IS None") #logging.info("_____________________") try: about = request.json["about"] except: about = None #logging.info("ABOUT IS None") # logging.info("_____________________") if (not isString([name, email, username, about])): #logging.info("one or more params not string") return json.dumps({"code": 2, "response": error_messages[2]}) sql = "SELECT idUser FROM User WHERE email = %s" cursor.execute(sql, [email]) if (cursor.fetchone() != None): #logging.info(email + " is already exists") return json.dumps({"code": 5, "response": error_messages[5]}) #isAnonymous = False #if ("isAnonymous" in request.json): isAnonymous = request.json.get("isAnonymous", False) sql = "INSERT INTO User(username, email, name, about, isAnonymous) VALUES (%s, %s, %s, %s, %s)" cursor.execute(sql, [username, email, name, about, isAnonymous]) sql = "SELECT MAX(idUser) FROM User" cursor.execute(sql) idU = cursor.fetchone()[0] # data = {} # data['about'] = about # data['email'] = email # data['id'] = idU # data['isAnonymous'] = isAnonymous # data['name'] = name # data['username'] = username data = { 'about': about, 'email': email, 'id': idU, 'isAnonymous': isAnonymous, 'username': username, } answer = {"code": 0, "response": data} response = json.dumps(answer) # logging.info("================SUCCESSFUL USER CREATION\n") tac = time() MyTime = tac - tic if MyTime > LimitTime: print(MyTime, "/db/api/user/create/ +++POST") return response
def createPost(): tic = time() # logging.info("================Post CREATION\n") #logging.info("Request : ") #logging.info(request.json) #logging.info(request.json["thread"]) try: thread = request.json["thread"] message = request.json["message"] date = request.json["date"] user = request.json["user"] forum = request.json["forum"] #logging.info("Thread : " + str(thread)) #logging.info("Message : " + message) #logging.info("Date : " + str(date)) # logging.info("User : "******"Forum : " + str(forum)) except: return json.dumps({"code": 2, "response": error_messages[2]}) parent = request.json.get('parent', None) isApproved = request.json.get('isApproved', False) isHighlighted = request.json.get('isHighlighted', False) isEdited = request.json.get('isEdited', False) isSpam = request.json.get('isSpam', False) isDeleted = request.json.get('isDeleted', False) try: id_Forum = getForumDetailsByShortName(forum)["id"] id_User = getUserInfoByEmail(user)["id"] except: return json.dumps({"code": 2, "response": error_messages[2]}) sql = "INSERT INTO Post(parent, isApproved, isHighlighted, isEdited, isSpam, isDeleted, likes, dislikes, date, message, idForum, idThread, idAuthor) " \ "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" cursor.execute(sql, [ parent, isApproved, isHighlighted, isEdited, isSpam, isDeleted, 0, 0, date, message, id_Forum, thread, id_User ]) sql = "SELECT MAX(idPost) FROM Post" cursor.execute(sql) idP = cursor.fetchone()[0] answer = {} answer["id"] = idP answer["parent"] = parent answer["isApproved"] = isApproved answer["isHighlighted"] = isHighlighted answer["isEdited"] = isEdited answer["isSpam"] = isSpam answer["isDeleted"] = isDeleted answer["likes"] = 0 answer["dislikes"] = 0 answer["points"] = answer["likes"] - answer["dislikes"] answer["date"] = date answer["message"] = message answer["forum"] = forum answer["thread"] = thread answer["user"] = user response = json.dumps({"code": 0, "response": answer}) # logging.info(" Response : " + response) #logging.info("================SUCCESSFUL Post CREATION\n") tac = time() MyTime = tac - tic if MyTime > LimitTime: print(MyTime, "/db/api/post/create/ +++POST") return response
def validate_username(self, username): username = "******" + username.data + "'" cursor.execute("SELECT * FROM users WHERE username = " + username) res = cursor.fetchone() if res is not None: raise ValidationError('Please use a different username.')
from werkzeug.security import check_password_hash from app import cursor from app.models import User, Item id = 1 cursor.execute( "insert into items (name, type,value,rare) values ('长剑','weapon',3,1) RETURNING id" ) weapon_id = cursor.fetchone()[0] print(type(weapon_id))
def createUser(): tic = time() # logging.info("================USER CREATION") try: email = request.json["email"] except: return json.dumps({"code": 2, "response": error_messages[2]}) try: name = request.json["name"] except: name = None # logging.info("NAME IS None") # logging.info("_____________________") try: username = request.json["username"] except: username = None # logging.info("USERNAME IS None") #logging.info("_____________________") try: about = request.json["about"] except: about = None #logging.info("ABOUT IS None") # logging.info("_____________________") if (not isString([name, email, username, about])): #logging.info("one or more params not string") return json.dumps({"code": 2, "response": error_messages[2]}) sql = "SELECT idUser FROM User WHERE email = %s" cursor.execute(sql, [email]) if (cursor.fetchone() != None): #logging.info(email + " is already exists") return json.dumps({"code": 5, "response": error_messages[5]}) #isAnonymous = False #if ("isAnonymous" in request.json): isAnonymous = request.json.get("isAnonymous", False) sql = "INSERT INTO User(username, email, name, about, isAnonymous) VALUES (%s, %s, %s, %s, %s)" cursor.execute(sql, [username, email, name, about, isAnonymous]) sql = "SELECT MAX(idUser) FROM User" cursor.execute(sql) idU = cursor.fetchone()[0] # data = {} # data['about'] = about # data['email'] = email # data['id'] = idU # data['isAnonymous'] = isAnonymous # data['name'] = name # data['username'] = username data = { 'about': about, 'email': email, 'id': idU, 'isAnonymous': isAnonymous, 'username': username, } answer = {"code": 0, "response": data} response = json.dumps(answer) # logging.info("================SUCCESSFUL USER CREATION\n") tac =time() MyTime = tac - tic if MyTime > LimitTime: print (MyTime, "/db/api/user/create/ +++POST") return response
def createPost(): logging.info("================Post CREATION\n") logging.info("Request : ") logging.info(request.json) logging.info(request.json["thread"]) try: thread = request.json["thread"] message = request.json["message"] date = request.json["date"] user = request.json["user"] forum = request.json["forum"] logging.info("Thread : " + str(thread)) logging.info("Message : " + message) logging.info("Date : " + str(date)) logging.info("User : "******"Forum : " + str(forum)) except: return json.dumps({"code": 2, "response": error_messages[2]}) parent = getOptionalParameterOrDefault(request.json, "parent", None) isApproved = getOptionalParameterOrDefault(request.json, "isApproved", False) isHighlighted = getOptionalParameterOrDefault(request.json, "isHighlighted", False) isEdited = getOptionalParameterOrDefault(request.json, "isEdited", False) isSpam = getOptionalParameterOrDefault(request.json, "isSpam", False) isDeleted = getOptionalParameterOrDefault(request.json, "isDeleted", False) try: id_Forum = getForumDetailsByShortName(forum)["id"] id_User = getUserInfoByEmail(user)["id"] except: return json.dumps({"code": 2, "response": error_messages[2]}) sql = "INSERT INTO Post(parent, isApproved, isHighlighted, isEdited, isSpam, isDeleted, likes, dislikes, date, message, idForum, idThread, idAuthor) " \ "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" cursor.execute(sql, [parent, isApproved, isHighlighted, isEdited, isSpam, isDeleted, 0, 0, date, message, id_Forum, thread, id_User]) sql = "SELECT MAX(idPost) FROM Post" cursor.execute(sql) idP = cursor.fetchone()[0] answer = {} answer["id"] = idP answer["parent"] = parent answer["isApproved"] = isApproved answer["isHighlighted"] = isHighlighted answer["isEdited"] = isEdited answer["isSpam"] = isSpam answer["isDeleted"] = isDeleted answer["likes"] = 0 answer["dislikes"] = 0 answer["points"] = answer["likes"] - answer["dislikes"] answer["date"] = date answer["message"] = message answer["forum"] = forum answer["thread"] = thread answer["user"] = user response = json.dumps({"code": 0, "response": answer}) logging.info(" Response : " + response) logging.info("================SUCCESSFUL Post CREATION\n") return response