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 send(): from app import cursor cursor.execute('''SELECT productid,name,stock from Product where stock <50 ''') stock = cursor.fetchall() print stock if len(stock) > 0: while True: msg = MIMEText('we need order somthing,pls contact us!', 'plain','utf-8') msg['From'] = _format_addr('YuanClothShop <%s>' % from_addr) msg['Subject'] = Header('OrderList', 'utf-8').encode() with open("emails.txt","r") as f: emails = f.readlines() for to_addr in emails: msg['To'] = _format_addr('Supplier<%s>' % to_addr) server = smtplib.SMTP(smtp_server, 25) server.set_debuglevel(1) server.ehlo() server.starttls() server.login(from_addr, password) server.sendmail(from_addr, [to_addr], msg.as_string()) server.quit() break
def freeze_admin(): if (request.method == 'POST'): print "freeze applications" sql = "UPDATE main_table SET freeze_status = \'true\' WHERE status = \'submitted\'" try: cursor.execute(sql) db.commit() print sql except: print "Db error" sql = "SELECT application_no, name, position_applied FROM main_table WHERE status = '%s';" % "submitted" cursor.execute(sql) rows1 = list(cursor.fetchall()) rows = [] for i in range(len(rows1)): temp = [ rows1[i][0], rows1[i][1], rows1[i][2], str(rows1[i][0]) + ".pdf" ] rows.append(temp) print rows return render_template('admin_view_part1.html', email_=session['email'], rows=rows)
def delete_ip_raspberry(): if request.method == 'POST': unidade = str(request.form['name_unidade']) hostname = str(request.form['hostname']) try: sql = (f"DELETE FROM equipments WHERE hostname ='{hostname}'") print(sql) cursor.execute(sql) # json = {'Status':f'MÉTODO POST ATIVO: RASPBERRY EXCLUÍDO: HOSTNAME: {hostname} -> {hospital}'} # return jsonify(json) flash(f'Raspberry: {hostname} foi deletetado com sucesso!', "success") if unidade == 'HUR 1': return redirect(url_for('ip_rasp_hur1')) elif unidade == 'HGMI' : return redirect(url_for('ip_rasp_hgmi')) else: return redirect(url_for('ip_rasp_anexo')) except Error as e: json_error = {'Error':f' {e}'} return jsonify(json_error) else: request.method == 'GET' return redirect(url_for('index'))
def setArticle(self, image_name, artic_name, artic_descr, artic_categ): cursor.execute( 'INSERT INTO articles(image_name, artic_name, artic_descr) VALUES(%s, %s, %s)', (image_name, artic_name, artic_descr)) cursor.execute('INSERT INTO categories(artic_categ) VALUES(%s)', (artic_categ)) conn.commit() cursor.execute( 'SELECT artic_id FROM articles WHERE artic_name=%s ORDER By artic_id DESC LIMIT 1', (artic_name)) artic_ind = (cursor.fetchone()) cursor.execute( 'SELECT categ_id FROM categories WHERE artic_categ=%s ORDER By categ_id DESC LIMIT 1', (artic_categ)) categ_ind = cursor.fetchone() cursor.execute( 'INSERT INTO cat_art(artic_id, categ_id) VALUES(%s, %s)', (artic_ind[0], categ_ind[0])) conn.commit()
def get_products(): sql = "SELECT * FROM products" status = 0, res = '' try: cursor.execute(sql) result = cursor.fetchall() print(type(result)) data = [] for rec in result: product = { 'id': rec[0], 'product_name': rec[1], 'product_description': rec[2], 'product_price': rec[3], 'product_qty': rec[4], 'created_by': rec[5], 'actions': rec[6] } data.append(product) status = 200 res = data except: print("Error: unable to fetch data") return res, status
def loginval(): global email global password global displayname error = "" try: email = request.form.get('email') password = request.form.get('password') cursor.execute( """SELECT * FROM `userfashion` WHERE `email` LIKE '{}'""".format( email)) userfashion = cursor.fetchone()[3] if len(userfashion) > 0: if sha256_crypt.verify(password, userfashion): cursor.execute( """SELECT * FROM `userfashion` WHERE `email` LIKE '{}'""". format(email)) user = cursor.fetchone() displayname = user[1] session['user_id'] = True flash('account logged in') return redirect(url_for('home')) else: error = "Wrong Email or Password" return render_template('login.html', error=error) except Exception as e: error = "invalid email. Pls register" return render_template('login.html', error=error)
def getSubscriptions(idUser): sql = "SELECT idThread FROM Subscription WHERE idUser = %s" cursor.execute(sql, [idUser]) subscriptions = getArrayEmailsFromDoubleDictionary(cursor.fetchall()) # logging.info(" Subscriptions of USER (" + str(idUser) + ") : " + str(subscriptions)) return subscriptions
def dashboard_add(): location = request.form.get('searchlocation') cursor.execute( """SELECT * FROM `admindatabase` WHERE `location` LIKE '{}'""".format(location)) posts = cursor.fetchall() print(posts) return render_template('userdashboard.html', posts=posts)
def delete(self, fields): try: sql = AllQuery.delete(self.table_name(self), fields) cursor.execute(sql) conn.commit() except ConnectionError as error: pass
def ip_hgmi(): #QUERY ALTERADA PARA TESTE sql = ( "SELECT id, ur, ip, hostname, unidade, local, setor, ramal FROM equipments WHERE unidade = 'HGMI' AND raspberry LIKE 'N%' " ) cursor.execute(sql) dados = [] unidade = "HGMI" for (id, ur, ip, hostname, unidade, local, setor, ramal) in cursor: dados.append({ "id": id, "ur": ur, "ip": ip, "hostname": hostname, "unidade": unidade, "local": local, "setor": setor, "ramal": ramal }) # print(dados) return render_template('public/maquinas.html', dados=dados, unidade=unidade)
def voteThread(): logging.info("================THREAD VOTE=====================") if "vote" in request.json and "thread" in request.json: vote = request.json["vote"] thread = request.json["thread"] else: return json.dumps({"code": 2, "response": error_messages[2]}) logging.info(" vote : " + str(vote) + "; thread : " + str(thread)) if vote == 1: addition = " likes = likes + 1" elif vote == -1: addition = " dislikes = dislikes + 1" else: logging.info(" incorrect vote param : " + str(vote)) return json.dumps({"code": 2, "response": error_messages[2]}) sql = "UPDATE Thread SET" + addition + " WHERE idThread = %s" cursor.execute(sql, [thread]) answer = getThreadDetailsByID(thread, []) response = json.dumps({"code": 0, "response": answer}) logging.info(" Response: ") logging.info(response) logging.info("================THREAD VOTE END=================") return response
def getListPostsOfThread(thread, since, order, limit): logging.info(" GETTING LIST POSTS BY THREAD") sql = "SELECT * FROM Post WHERE idThread = %s" params = [thread] if since: sql += " AND date >= %s" params.append(since) sql += " ORDER BY date " + order if limit: sql += " LIMIT %s" params.append(int(limit)) logging.info(" Final SQL listPosts : " + sql) logging.info(" Final PARAMS listPosts : " + str(params)) cursor.execute(sql, params) result = cursor.fetchall() answer = getArrayPostsFormDDictionary(result, []) logging.info(" GETTED POSTS : ") logging.info(answer) logging.info(" ==============") return answer
def unsubscribeThread(): tic = time() # logging.info("=====================UNSUBSCRIBE THREAD======================") try: user = request.json["user"] thread = request.json["thread"] except: return json.dumps({"code": 2, "response": error_messages[2]}) idUser = getUserIdByEmail(user) if not idUser: return json.dumps({"code": 1, "response": error_messages[1]}) # logging.info(" User : "******"; idThread : " + str(thread)) sql = "DELETE FROM Subscription WHERE idUser = %s AND idThread = %s" cursor.execute(sql, [idUser, thread]) response = json.dumps({ "code": 0, "response": { "thread": thread, "user": user } }) #logging.info(" Result : " + response) #logging.info("=====================UNSUBSCRIBE THREAD SUCCESS======================") tac = time() MyTime = tac - tic if MyTime > LimitTime: print(MyTime, "/db/api/thread/unsubscribe/ +++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 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 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 unfollow(): tic = time() # logging.info("================USER UNFOLLOW========================") try: follower = request.json["follower"] followee = request.json["followee"] except: return json.dumps({"code": 2, "response": error_messages[2]}) try: idFollower = getUserIdByEmail(follower) idFollowee = getUserIdByEmail(followee) except: return json.dumps({"code": 1, "response": error_messages[1]}) # logging.info(" follower : " + str(idFollower)) # logging.info(" followee : " + str(idFollowee)) sql = "DELETE FROM Follower WHERE idFollower = %s AND idFollowing = %s" cursor.execute(sql, [idFollower, idFollowee]) answer = getUserInfoByID(idFollower) response = json.dumps({"code": 0, "response": answer}) # logging.info("================USER UNFOLLOW END====================") tac =time() MyTime = tac - tic if MyTime > LimitTime: print (MyTime, "/db/api/user/unfollow/ +++POST") return response
def getListPostsOfForum(forum, since, order, limit, related): #logging.info(" GETTING LIST POSTS BY FORUM") idForum = getForumIdByShortName(forum) sql = "SELECT * FROM Post WHERE idForum = %s" params = [idForum] if since: sql += " AND date >= %s" params.append(since) sql += " ORDER BY date " + order if limit: sql += " LIMIT %s" params.append(int(limit)) # logging.info(" Final SQL listPosts : " + sql) #logging.info(" Final PARAMS listPosts : " + str(params)) cursor.execute(sql, params) result = cursor.fetchall() answer = getArrayPostsFormDDictionary(result, related) #logging.info(" GETTED POSTS : ") # logging.info(answer) #logging.info(" ==============") return answer
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 subscribeThread(): tic = time() #logging.info("=======================SUBSCRIBE THREAD======================") try: user = request.json["user"] thread = request.json["thread"] except: return json.dumps({"code": 2, "response": error_messages[2]}) idUser = getUserIdByEmail(user) if not idUser: return json.dumps({"code": 1, "response": error_messages[1]}) #logging.info(" User : "******"; idThread : " + str(thread)) sql = "INSERT INTO Subscription(idUser, idThread) VALUES(%s,%s)" try: cursor.execute(sql, [idUser, thread]) except: return json.dumps({"code": 5, "response": error_messages[5]}) response = json.dumps({"code": 0, "response": {"thread": thread, "user": user}}) #logging.info(" Result : " + response) #logging.info("=======================SUBSCRIBE THREAD SUCCESS======================") tac =time() MyTime = tac - tic if MyTime > LimitTime: print (MyTime, "/db/api/thread/subscribe/ +++POST") return response
def follow(): logging.info("================USER FOLLOW========================") try: follower = request.json["follower"] followee = request.json["followee"] except: return json.dumps({"code": 2, "response": error_messages[2]}) try: idFollower = getUserIdByEmail(follower) idFollowee = getUserIdByEmail(followee) except: return json.dumps({"code": 1, "response": error_messages[1]}) logging.info(" follower : " + str(idFollower)) logging.info(" followee : " + str(idFollowee)) sql = "INSERT INTO Follower(idFollower, idFollowing) VALUES(%s, %s)" try: cursor.execute(sql, [idFollower, idFollowee]) except: json.dumps({"code": 5, "response": error_messages[5]}) emailsFollowers = getFollowerEmails(idFollower, None, None, None) emailsFollowing = getFollowingEmails(idFollower, None, None, None) userInfo = getUserInfoByID(idFollower) userInfo["followers"] = emailsFollowers userInfo["following"] = emailsFollowing userInfo["subscriptions"] = [] logging.info("Response : ") logging.info(userInfo) response = json.dumps({"code": 0, "response": userInfo}) logging.info("================USER FOLLOW SUCCESS================\n") return response
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 getSubscriptions(idUser): sql = "SELECT idThread FROM Subscription WHERE idUser = %s" cursor.execute(sql, [idUser]) subscriptions = getArrayEmailsFromDoubleDictionary(cursor.fetchall()) logging.info(" Subscriptions of USER (" + str(idUser) + ") : " + str(subscriptions)) return subscriptions
def votePost(): logging.info("================POST VOTE=====================") if "vote" in request.json and "post" in request.json: vote = request.json["vote"] post = request.json["post"] else: return json.dumps({"code": 2, "response": error_messages[2]}) logging.info(" vote : " + str(vote) + "; post : " + str(post)) if vote == 1: addition = " likes = likes + 1" elif vote == -1: addition = " dislikes = dislikes + 1" else: logging.info(" incorrect vote param : " + str(vote)) return json.dumps({"code": 2, "response": error_messages[2]}) sql = "UPDATE Post SET" + addition + " WHERE idPost = %s" cursor.execute(sql, [post]) answer = getPostDetailsByID(post, []) response = json.dumps({"code": 0, "response": answer}) logging.info(" Response: ") logging.info(response) logging.info("================POST VOTE END=================") return response
def unsubscribeThread(): tic = time() # logging.info("=====================UNSUBSCRIBE THREAD======================") try: user = request.json["user"] thread = request.json["thread"] except: return json.dumps({"code": 2, "response": error_messages[2]}) idUser = getUserIdByEmail(user) if not idUser: return json.dumps({"code": 1, "response": error_messages[1]}) # logging.info(" User : "******"; idThread : " + str(thread)) sql = "DELETE FROM Subscription WHERE idUser = %s AND idThread = %s" cursor.execute(sql, [idUser, thread]) response = json.dumps({"code": 0, "response": {"thread": thread, "user": user}}) #logging.info(" Result : " + response) #logging.info("=====================UNSUBSCRIBE THREAD SUCCESS======================") tac =time() MyTime = tac - tic if MyTime > LimitTime: print (MyTime, "/db/api/thread/unsubscribe/ +++POST") return response
def unfollow(): tic = time() # logging.info("================USER UNFOLLOW========================") try: follower = request.json["follower"] followee = request.json["followee"] except: return json.dumps({"code": 2, "response": error_messages[2]}) try: idFollower = getUserIdByEmail(follower) idFollowee = getUserIdByEmail(followee) except: return json.dumps({"code": 1, "response": error_messages[1]}) # logging.info(" follower : " + str(idFollower)) # logging.info(" followee : " + str(idFollowee)) sql = "DELETE FROM Follower WHERE idFollower = %s AND idFollowing = %s" cursor.execute(sql, [idFollower, idFollowee]) answer = getUserInfoByID(idFollower) response = json.dumps({"code": 0, "response": answer}) # logging.info("================USER UNFOLLOW END====================") tac = time() MyTime = tac - tic if MyTime > LimitTime: print(MyTime, "/db/api/user/unfollow/ +++POST") return response
def voteThread(): tic = time() #logging.info("================THREAD VOTE=====================") if "vote" in request.json and "thread" in request.json: vote = request.json["vote"] thread = request.json["thread"] else: return json.dumps({"code": 2, "response": error_messages[2]}) #logging.info(" vote : " + str(vote) + "; thread : " + str(thread)) if vote == 1: addition = " likes = likes + 1" elif vote == -1: addition = " dislikes = dislikes + 1" else: # logging.info(" incorrect vote param : " + str(vote)) return json.dumps({"code": 2, "response": error_messages[2]}) sql = "UPDATE Thread SET" + addition + " WHERE idThread = %s" cursor.execute(sql, [thread]) answer = getThreadDetailsByID(thread, []) response = json.dumps({"code": 0, "response": answer}) # logging.info(" Response: ") # logging.info(response) # logging.info("================THREAD VOTE END=================") tac =time() MyTime = tac - tic if MyTime > LimitTime: print (MyTime, "/db/api/thread/vote/ +++POST") return response
def get_all_tasks(): sql = "SELECT * FROM tasks" status = 0 res = '' try: cursor.execute(sql) result = cursor.fetchall() data = [] for rec in result: task = { 'task_id': rec[0], 'robot_id': rec[1], 'task_name': rec[2], 'issued_time': json.dumps(rec[3], default=str), 'scheduled_time': json.dumps(rec[4], default=str), 'status': rec[5] } data.append(task) status = 200 res = data except Exception as e: print("Error: unable to fetch data.") print(e) return res, status
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 getListPostsOfForum(forum, since, order, limit, related): logging.info(" GETTING LIST POSTS BY FORUM") idForum = getForumIdByShortName(forum) sql = "SELECT * FROM Post WHERE idForum = %s" params = [idForum] if since: sql += " AND date >= %s" params.append(since) sql += " ORDER BY date " + order if limit: sql += " LIMIT %s" params.append(int(limit)) logging.info(" Final SQL listPosts : " + sql) logging.info(" Final PARAMS listPosts : " + str(params)) cursor.execute(sql, params) result = cursor.fetchall() answer = getArrayPostsFormDDictionary(result, related) logging.info(" GETTED POSTS : ") logging.info(answer) logging.info(" ==============") return answer
def login(): global users_dic_work global users_dic_explore if current_user.is_authenticated: return redirect(url_for('index')) form = LoginForm() if form.validate_on_submit(): username = "******" + form.username.data + "'" cursor.execute("SELECT * FROM users WHERE username = " + username) res = cursor.fetchone() if res is None: flash('Invalid username') return redirect(url_for('login')) user = User(res) if not user.check_password(form.password.data): flash('Invalid password') return redirect(url_for('login')) login_user(user, remember=form.remember_me.data) users_dic_work[current_user.id] = 0 users_dic_explore[current_user.id] = 0 next_page = request.args.get('next') if not next_page or url_parse(next_page).netloc != '': next_page = url_for('index') return redirect(next_page) return render_template('login.html', title='Sign In', form=form)
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 index(): if not current_user.is_authenticated: return redirect(url_for('login')) users_dic_work[current_user.id] = 0 users_dic_explore[current_user.id] = 0 if current_user.weapon_id is not None: cursor.execute("SELECT * FROM items where id =" + str(current_user.weapon_id)) res = cursor.fetchone() weapon = Item(res) else: weapon = None cursor.execute( "select items.id, items.name, items.type, items.value, items.rare\ from items, users, armors\ where items.id=armors.armor_id and users.id=armors.user_id and users.id=%s" % current_user.id) res = cursor.fetchall() defense = 0 if len(res) == 0: armors = None else: armors = [] for i in res: armor = Item(i) defense = defense + armor.value armors.append(armor) return render_template("index.html", title='Home Page', weapon=weapon, armors=armors, defense=defense)
def subscribeThread(): tic = time() #logging.info("=======================SUBSCRIBE THREAD======================") try: user = request.json["user"] thread = request.json["thread"] except: return json.dumps({"code": 2, "response": error_messages[2]}) idUser = getUserIdByEmail(user) if not idUser: return json.dumps({"code": 1, "response": error_messages[1]}) #logging.info(" User : "******"; idThread : " + str(thread)) sql = "INSERT INTO Subscription(idUser, idThread) VALUES(%s,%s)" try: cursor.execute(sql, [idUser, thread]) except: return json.dumps({"code": 5, "response": error_messages[5]}) response = json.dumps({ "code": 0, "response": { "thread": thread, "user": user } }) #logging.info(" Result : " + response) #logging.info("=======================SUBSCRIBE THREAD SUCCESS======================") tac = time() MyTime = tac - tic if MyTime > LimitTime: print(MyTime, "/db/api/thread/subscribe/ +++POST") return response
def delete_user(data): sql = "SELECT * FROM users WHERE user_email = '%s'" % data["user_email"] print(sql) status = 0 res = '' try: cursor.execute(sql) result = cursor.fetchone() print(result[7]) if result[7] == 4 or result[7] == 15 or result[7] == 7: sql2 = "DELETE FROM users WHERE user_email = '%s'" % data[ "user_email"] print(sql2) cursor.execute(sql2) db.commit() status = 200 res = "User deleted successfully" else: status = 201 res = "Not authorized" except: print("Error: unable to fetch data") return res, status
def voteThread(): tic = time() #logging.info("================THREAD VOTE=====================") if "vote" in request.json and "thread" in request.json: vote = request.json["vote"] thread = request.json["thread"] else: return json.dumps({"code": 2, "response": error_messages[2]}) #logging.info(" vote : " + str(vote) + "; thread : " + str(thread)) if vote == 1: addition = " likes = likes + 1" elif vote == -1: addition = " dislikes = dislikes + 1" else: # logging.info(" incorrect vote param : " + str(vote)) return json.dumps({"code": 2, "response": error_messages[2]}) sql = "UPDATE Thread SET" + addition + " WHERE idThread = %s" cursor.execute(sql, [thread]) answer = getThreadDetailsByID(thread, []) response = json.dumps({"code": 0, "response": answer}) # logging.info(" Response: ") # logging.info(response) # logging.info("================THREAD VOTE END=================") tac = time() MyTime = tac - tic if MyTime > LimitTime: print(MyTime, "/db/api/thread/vote/ +++POST") return response
def create_user(user): password = hash_password(user["password"]) sql = "INSERT INTO users(user_name, user_email, user_password, user_address, user_phone, created_by, actions) VALUES ('%s','%s','%s','%s','%s', %d, %d)" % ( user["user_name"], user["user_email"], password, user["user_address"], user["user_phone"], user["created_by"], user["actions"]) status = 0 res = '' try: cursor.execute(sql) db.commit() status = 200 res = 'User created successfully' except pymysql.Error as e: try: print("MySQL Error [%d]: %s") % (e.args[0], e.args[1]) return None except IndexError: print("MySQL Error: %s") % str(e) return None except: db.rollback() status = 401 res = 'Something went wrong. Try again' return res, status
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 get_users(): sql = "SELECT * FROM users" status = 0 res = '' try: cursor.execute(sql) result = cursor.fetchall() data = [] for rec in result: user = { 'id': rec[0], 'user_name': rec[1], 'user_email': rec[2], 'user_address': rec[4], 'user_phone': rec[5], 'created_by': rec[6], 'actions': rec[7] } data.append(user) status = 200 res = data except: print("Error: unable to fetch data") return res, status
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 votePost(): tic = time() #logging.info("================POST VOTE=====================") if "vote" in request.json and "post" in request.json: vote = request.json["vote"] post = request.json["post"] else: return json.dumps({"code": 2, "response": error_messages[2]}) #logging.info(" vote : " + str(vote) + "; post : " + str(post)) if vote == 1: addition = " likes = likes + 1" elif vote == -1: addition = " dislikes = dislikes + 1" else: #logging.info(" incorrect vote param : " + str(vote)) return json.dumps({"code": 2, "response": error_messages[2]}) sql = "UPDATE Post SET" + addition + " WHERE idPost = %s" cursor.execute(sql, [post]) answer = getPostDetailsByID(post, []) response = json.dumps({"code": 0, "response": answer}) # logging.info(" Response: ") # logging.info(response) #logging.info("================POST VOTE END=================") tac = time() MyTime = tac - tic if MyTime > LimitTime: print(MyTime, "/db/api/post/vote/ +++POST") return response
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 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 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 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 threadsList(): tic = time() # logging.info("=====================================THREAD LIST BEGIN============================================") try: user = request.args.get("user") #logging.info("User : "******"forum") #logging.info("Forum : " + forum) except: forum = None if not user and not forum: return json.dumps({"code": 2, "response": error_messages[2]}) limit = getOptionalGetParameterOrDefault(request.args, "limit", None) order = getOptionalGetParameterOrDefault(request.args, "order", "desc") since = getOptionalGetParameterOrDefault(request.args, "since", None) sql = "SELECT * FROM Thread WHERE 1 = 1 " params = [] if user: sql = sql + " AND idAuthor = %s" idAuthor = getUserIdByEmail(user) params.append(idAuthor) if forum: sql = sql + " AND idForum = %s" idForum = getForumIdByShortName(forum) params.append(idForum) if since: sql = sql + " AND DATE(date) >= %s" #TODO optimizate date query params.append(since) sql = sql + " ORDER BY date " + order if limit: sql = sql + " LIMIT " + str(limit) # logging.info("FINAL SQL = " + sql) #logging.info("FINAL PARAMS = " + str(params)) cursor.execute(sql, params) data = cursor.fetchall() answer = [] for item in data: answer.append(getThreadDetailsByID(item[0], [])) response = json.dumps({"code": 0, "response": answer}) # logging.info("Response : ") #logging.info(response) #logging.info("=====================================THREAD LIST END============================================") tac =time() MyTime = tac - tic if MyTime > LimitTime: print (MyTime, "/db/api/thread/list/") 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 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 updateThread(): logging.info("=======================UPDATE THREAD==========================") try: message = request.json["message"] slug = request.json["slug"] thread = request.json["thread"] except: return json.dumps({"code": 2, "response": error_messages[2]}) sql = "UPDATE Thread SET message = %s, slug = %s WHERE idThread = %s" cursor.execute(sql, [message, slug, thread]) answer = getThreadDetailsByID(thread, []) response = json.dumps({"code": 0, "response": answer}) logging.info("=======================UPDATE THREAD SUCCESS==================") return response
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 updateProfile(): logging.info("=======================UPDATING PROFILE========================") try: about = request.json["about"] user = request.json["user"] name = request.json["name"] except: logging.error(" no required fields") return json.dumps({"code": 2, "response": error_messages[2]}) sql = "UPDATE User SET about = %s, name = %s WHERE email = %s" cursor.execute(sql, [about, name, user]) result = getUserInfoByEmail(user) response = json.dumps({"code": 0, "response": result}) logging.info(" Result : " + response) logging.info("=======================UPDATING PROFILE SUCCESSFUL=============") return response
def userListPosts(): tic = time() # logging.info("================USER LISTPOSTS================") try: user = request.args.get("user") except: return json.dumps({"code": 2, "response": error_messages[2]}) limit = getOptionalGetParameterOrDefault(request.args, "limit", None) order = getOptionalGetParameterOrDefault(request.args, "order", "desc") since = getOptionalGetParameterOrDefault(request.args, "since", None) idUser = getUserIdByEmail(user) if not idUser: return json.dumps({"code": 1, "response": error_messages[1]}) sql = "SELECT P.* FROM Post P INNER JOIN User U ON P.idAuthor = U.idUser WHERE U.idUser = %s" params = [idUser] if since: params.append(since) sql += " AND P.date >= %s" sql += " ORDER BY P.date " + order if limit: params.append(int(limit)) sql += " LIMIT %s" # logging.info(" Final SQL listPosts : " + sql) # logging.info(" Final PARAMS listPosts : " + str(params)) cursor.execute(sql, params) result = getArrayDictFromDoubleDictionary(cursor.fetchall()) # logging.info(" Results : ") response = json.dumps({ "code": 0, "response": result}) # logging.info(" Response : " + response) # logging.info("================USER LISTPOSTS END============") tac = time() MyTime = tac - tic if MyTime > LimitTime: print (MyTime, "/db/api/user/listPosts/") 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 getFollowingEmails(idUser, since, order, limit): sql = "SELECT U.email FROM Follower F INNER JOIN User U ON F.idFollowing = U.idUser WHERE F.idFollower = %s" params = [idUser] if since: sql += " AND U.idUser > %s" params.append(int(since)) if not order: order = "desc" sql += " ORDER BY U.username " + order if limit: sql += " LIMIT %s" params.append(int(limit)) logging.info(" Final SQL followingEmails : " + sql) logging.info(" Final PARAMS followingEmails : " + str(params)) cursor.execute(sql, params) emails = getArrayEmailsFromDoubleDictionary(cursor.fetchall()) logging.info(" EMAILS FOLLOWING OF USER (" + str(idUser) + ") : " + str(emails)) return emails
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