def update_useravatar(public_id): if not public_id : return jsonify ({"status" : "error" , "message":"Missing PublicId"}) , 404 try : with connection.cursor() as cursor: sql = "SELECT * FROM user where user.public_id = %s" cursor.execute(sql, (public_id,)) rv = cursor.fetchall() if not rv: return jsonify ({"status" : "error" , "message":"Failed to find users"}) , 404 connection.commit() cursor.close() except : return jsonify ({"status" : "error" , "message":"Connection Error "}) , 500 try: if request.method == 'POST': if 'file' not in request.files: return jsonify ({"Code" : "002" , "Message":"NO files to Upload"}) file = request.files['file'] if file.filename == '': return jsonify ({"Code" : "002" , "Message":"No selected file"}) if file and allowed_file(file.filename): filename = secure_filename(file.filename) extension = filename.split('.')[1] newfilename = 'publicid-' + public_id+('-') + 'avatar.' + extension file.save(os.path.join(UPLOAD_FOLDER, newfilename)) with connection.cursor() as cursor: sql = "UPDATE usersdetails SET usersdetails_avatar = %s WHERE user_public_id = %s" cursor.execute(sql, (newfilename,public_id,)) connection.commit() cursor.close() return jsonify(filename=newfilename),200 except: return jsonify ({"Code" : "002" , "Message":"Failed to UpdateData Avatar"}),500
def changejobStatus(current_user,public_job,parameter): try: public_id = current_user["public_id"] except: return jsonify({"Status": "Failed", "message": "Error DecodeId"}), 500 if parameter is None: return jsonify({"Status": "Failed", "message": "Error Parameter???"}), 500 if public_job is None: return jsonify({"Status": "Failed", "message": "Error public_job???"}), 500 if parameter == 3 : try: with connection.cursor() as cursor: sql = (" UPDATE job SET job.job_operator_id = %s WHERE job.job_public_id = %s " ) cursor.execute(sql, (public_id,public_job,)) connection.commit() cursor.close() except Exception as e: return jsonify({"Status": "Error", "projectList": e}), 500 try: with connection.cursor() as cursor: sql = (" UPDATE job SET job.status_id = %s WHERE job.job_public_id = %s " ) cursor.execute(sql, (parameter,public_job,)) connection.commit() cursor.close() return jsonify({"Status": "success", "jobList": "Update Status success"}), 200 except Exception as e: return jsonify({"Status": "Error", "projectList": e}), 500
def upload_image(): if request.files: image_file = request.files.get("imageToUpload", "") all_followers = int(request.form.get("allFollowers") != None) caption = request.form.get("caption") image_name = image_file.filename filepath = os.path.join(IMAGES_DIR, image_name) image_file.save(filepath) photo_id = None query = "INSERT INTO photo (photoOwner, timestamp, filePath, allFollowers, caption) VALUES (%s, %s, %s, %s, %s)" with connection.cursor() as cursor: cursor.execute(query, (session["username"], time.strftime('%Y-%m-%d %H:%M:%S'), image_name, all_followers, caption)) photo_id = cursor.lastrowid if not all_followers: query = "INSERT INTO Share (groupName, groupOwner, photoID) VALUES (%s, %s, %s)" groups = request.form.getlist('group') for group in groups: group_divided_to_list = group.split(",") groupName = group_divided_to_list[0] groupOwner = group_divided_to_list[1] with connection.cursor() as cursor: cursor.execute(query, (groupName, groupOwner, photo_id)) message = "Image has been successfully uploaded." return render_template("upload.html", message=message, user_groups=tools._get_current_user_groups()) else: message = "Failed to upload image." return render_template("upload.html", message=message, user_groups=tools._get_current_user_groups())
def submit_tag_action(): request_data = request.form response = request_data["response"] photoid = request_data["photoid"] if response == "accept": query = "update tag set acceptedTag = 1 where photoid = %s and username = %s" with connection.cursor() as cursor: cursor.execute(query, (photoid, session["username"])) elif response == "decline": query = "delete from tag where photoid = %s and username = %s" with connection.cursor() as cursor: cursor.execute(query, (photoid, session["username"]))
def show_user(id): if not id: return jsonify({"Code": "001", "Message": "id is empty"}) try: user = User.query.filter_by(id=id).first() userId = user.id publicId = user.public_id companyId = user.company_id roleId = user.role_id username = user.username company = Company.query.filter_by(company_id=companyId).first() companyName = company.company_name role = Role.query.filter_by(role_id=roleId).first() rolename = role.role_name with connection.cursor() as cursor: sql = "SELECT usersdetails_email ,usersdetails_firstname , usersdetails_lastname ,usersdetails_phone , usersdetails_position ,usersdetails_avatar , usersdetails_employeenumber FROM usersdetails LEFT JOIN user on user.public_id = usersdetails.user_public_id "\ " WHERE user.public_id = %s" cursor.execute(sql, (publicId, )) rv = cursor.fetchall() connection.commit() cursor.close() return jsonify( { "userId": userId, "username": username, "publicId": publicId, "companyId": companyId, "companyName": companyName, "roleId": roleId, "rolename": rolename }, {"usersdetails": rv}) except Exception as e: print(e) return jsonify({"Code": "002", "Message": "User Not found"})
def CountStatusUserJob(current_user,projectid): try: public_id = current_user["public_id"] except: return jsonify({"Status": "Failed", "message": "Error DecodeId"}), 200 try: with connection.cursor() as cursor: # Read a single record sql = ( " SELECT COUNT(job.job_public_id) as count , status.status_name , job.status_id from job" " LEFT JOIN project_has_job on project_has_job.job_public_id = job.job_public_id" " LEFT JOIN status on status.status_id = job.status_id" " LEFT JOIN teamproject on project_has_job.teamproject_public_id = teamproject.teamproject_public_id" " Left join teamproject_has_user on teamproject_has_user.teamproject_public_id = teamproject.teamproject_public_id" " LEFT JOIN user on teamproject_has_user.user_public_id = user.public_id" " WHERE teamproject_has_user.user_public_id = %s and project_has_job.project_public_id = %s " " GROUP BY job.status_id" ) cursor.execute(sql, (public_id,projectid,)) rv = cursor.fetchall() connection.commit() cursor.close() return jsonify({"Status": "success", "projectCount": rv}), 200 except Exception as e: return jsonify({"Status": "Error", "projectList": e}), 500
def ListAllProjectByGroup(current_user, public_project): try: public_id = current_user["public_id"] except: return jsonify({"Status": "Failed", "message": "Error DecodeId"}), 500 try: with connection.cursor() as cursor: # Read a single record sql = ( " SELECT job.job_name , job.job_public_id , status.status_name , job.status_id,priority.priority_name,teamproject.teamproject_public_id ,job.job_created , jobdetails.jobdetails_enddate from job LEFT JOIN project_has_job ON job.job_public_id = project_has_job.job_public_id " "LEFT JOIN priority on priority.priority_id = job.priority_id" " LEFT JOIN jobdetails on jobdetails.job_public_id = job.job_public_id " " LEFT JOIN status on status.status_id = job.status_id " " LEFT JOIN teamproject on teamproject.teamproject_public_id = project_has_job.teamproject_public_id" " LEFT JOIN teamproject_has_user on teamproject_has_user.teamproject_public_id = teamproject.teamproject_public_id" " WHERE project_has_job.project_public_id = %s and teamproject_has_user.user_public_id = %s ORDER BY status.status_id" ) cursor.execute(sql, (public_project, public_id)) rv = cursor.fetchall() connection.commit() cursor.close() arr = dict() for r in rv: val = [] status = r["status_name"] arr[status] = {} for a in rv: if status == a["status_name"]: val.append(a) arr[status] = val return jsonify({"Status": "Success", "projectList": arr}), 200 except: return jsonify({"Status": "Failed", "message": "Error DecodeId"}), 500
def ListAssetsJob(current_user, job_public): try: public_id = current_user["public_id"] except: return jsonify({"Status": "Failed", "message": "Error DecodeId"}), 200 try: with connection.cursor() as cursor: sql = ( " SELECT assets_public_id , assets_sn , users_creator_id ,assets_brand_name , assets_categories_name , assets_series_name , assets_insurance_name " " FROM assets LEFT JOIN jobhasassets on jobhasassets.assets_id = assets.assets_id " " LEFT JOIN job on job.job_id = jobhasassets.job_id " " LEFT JOIN assetsbrand on assetsbrand.assets_brand_id = assets.assets_brand_id" " LEFT JOIN assetscategories on assetscategories.assets_categories_id = assets.assets_categories_id" " LEFT JOIN assetsinsurance on assetsinsurance.assets_insurance_id = assets.assets_insurance_id" " LEFT JOIN assetsseries on assetsseries.assets_series_id = assets.assets_series_id" " LEFT JOIN assetsbrand on assetsbrand.assets_brand_id = assets.assets_brand_id" " LEFT JOIN assetscategories on assetscategories.assets_categories_id = assets.assets_categories_id" " LEFT JOIN assetsinsurance on assetsinsurance.assets_insurance_id = assets.assets_insurance_id" " LEFT JOIN assetsseries on assetsseries.assets_series_id = assets.assets_series_id" " WHERE job.job_public_id = %s ") cursor.execute(sql, (job_public)) rv = cursor.fetchall() connection.commit() cursor.close() return jsonify({"Status": "success", "assets": rv}), 200 except Exception as e: return jsonify({"Status": "Error", "projectList": e}), 500
def ListProjectByStatus(current_user, status_id): try: public_id = current_user["public_id"] except: return jsonify({"Status": "Failed", "message": "Error DecodeId"}), 200 if status_id is None: return jsonify({"Status": " Failed ", "message": "Error Status not"}), 500 try: with connection.cursor() as cursor: # Read a single record sql = ( " SELECT project.project_public_id , project_name , status_name , teamproject.teamproject_public_id , teamproject_name from project " " LEFT JOIN teamproject_has_project on teamproject_has_project.project_public_id = project.project_public_id " " LEFT JOIN teamproject on teamproject_has_project.teamproject_public_id = teamproject.teamproject_public_id" " LEFT JOIN teamproject_has_user on teamproject_has_user.teamproject_public_id = teamproject.teamproject_public_id " " LEFT JOIN status on status.status_id = teamproject_has_project.status_id" " WHERE teamproject_has_user.user_public_id = %s AND teamproject_has_project.status_id = %s" ) cursor.execute(sql, (public_id, status_id,)) rv = cursor.fetchall() connection.commit() cursor.close() return jsonify({"Status": "success", "projectList": rv}), 200 except Exception as e: return jsonify({"Status": "Error", "projectList": e}), 500
def get_user_tag_requests(): result = None query = "select photoid, filepath from photo natural join tag where username = %s and acceptedTag != True" with connection.cursor() as cursor: cursor.execute(query, session["username"]) result = cursor.fetchall() return result
def index(): with connection.cursor() as cursor: sql = "SELECT MovieID, Name FROM Movie WHERE `Now Playing` = 1" cursor.execute(sql,args=None) result = cursor.fetchall() return render_template('index.html',title = 'Home', movieData = result)
def submit_like(): request_data = request.form # get the proper form photoid = request_data["photoid"] # get the photoid of the liked photo query = "insert into liked (username, photoID) VALUES (%s, %s)" # prepare the query with connection.cursor() as cursor: cursor.execute(query, (session["username"], photoid)) # submit the photo
def database_connection_list_cold(connection, user_data): cursor = connection.cursor() array = tuple(user_data.keys()) sql_query = "update list_juces set checkbox_yes_no = ' '" sql_query_yes = "update list_juces set checkbox_yes_no = 'yes' WHERE available_juices IN %s" print(cursor.mogrify(sql_query, (array, ))) cursor.execute(sql_query) cursor.execute(sql_query_yes, (array, )) connection.commit() cursor.close()
def database_connect_function(): try: connection = psycopg2.connect(user="******", host="127.0.0.1", port="5432", database="officecafe") cursor = connection.cursor() cursor.execute("select available_juices from list_juces") record = cursor.fetchall() return record except (Exception, psycopg2.Error) as error: print("Error in connecting to PSQL", error)
def submit_comment(): request_data = request.form comment = request_data["comment"] # get comment from form data photoid = request_data["photoid"] # get photoid from form data query = "insert into comment (username, photoID, commentText) VALUES (%s, %s, %s)" #timestamp is generated by db try: with connection.cursor() as cursor: cursor.execute(query, (session["username"], photoid, comment)) except: # decided to fail silently rather than change the db schema bc we like that each user is limited in the number of # comments. it reduces spam for a user print("fail silently for duplicate entries or other db failures")
def new_tag(): if request.form: requestData = request.form tagged_name = requestData["username"] photoID = requestData["photoID"] user = session["username"] print(user + " tagged " + tagged_name + " in photoID: " + photoID) query = "INSERT into Tag (username, photoID, acceptedTag) VALUES (%s, %s, %s)" #if the user is tagging themselves if(user == tagged_name): with connection.cursor() as cursor: cursor.execute(query, (tagged_name, photoID, 1)) #if they are tagging someone else else: #if query is visiable to the tagged_name then the result (tagged_name, photoID, 0) query2 = 'SELECT Photo.photoID, timestamp, filePath, photoOwner, caption FROM Photo, Belong, Share Where belong.username = %s and belong.groupOwner = share.groupOwner AND Belong.groupName = share.groupName AND photo.photoID = share.photoID UNION (SELECT Photo.photoID, timestamp, filePath, photoOwner, caption FROM Photo, Follow WHERE (photoOwner = %s ) or (followerUsername = %s AND photoOwner = followeeUsername AND acceptedfollow = TRUE)) ORDER BY Timestamp DESC' with connection.cursor() as cursor: cursor.execute(query2, (tagged_name, tagged_name, tagged_name)) vPhoto = cursor.fetchall() isVisible= 0; #true/false to determine if the person can view the photo print(vPhoto) for photo in vPhoto: #print(photo) #print(photo['photoID']) currentPhoto = int(photo['photoID']) #print("Does the local number above match tag number below") #print(photoID) intphoto = int(photoID) if(currentPhoto == intphoto): #print("here") isVisible= 1 with connection.cursor() as cursor: cursor.execute(query, (tagged_name, photoID, 0)) if(isVisible == 0): return render_template('/home.html') return render_template('/images.html')
def show_user_by_publicId(current_user,public_id): try : with connection.cursor() as cursor: sql =( "SELECT company_name , username,usersdetails_firstname ,usersdetails_lastname,usersdetails_avatar,usersdetails_email,usersdetails_phone,usersdetails_position FROM usersdetails " "LEFT JOIN user on user.public_id = usersdetails.user_public_id " "LEFT JOIN company on company.company_id = user.company_id" " WHERE usersdetails.user_public_id = %s") cursor.execute(sql, (public_id,)) rv = cursor.fetchall() connection.commit() cursor.close() return jsonify( {"usersdetails" :rv} ) except Exception as e: print (e) return jsonify ({"Code" : "002" , "Message":"User Not found"})
def Listproject(current_user,project_id): try: user_public_id = current_user["public_id"] except Exception as e: return jsonify({"Status": "Failed", "message": "Error DecodeId" + str(e)}), 500 with connection.cursor() as cursor: # Read a single record sql = (" SELECT * from project LEFT JOIN status on status.status_id = project.status_id " " LEFT JOIN projectdetails on projectdetails.project_public_id = project.project_public_id" " WHERE project.project_public_id = %s " ) cursor.execute(sql,(project_id,)) result = cursor.fetchall() cursor.close() return jsonify({"Status" : "success" ,"projectList": result } ) , 200
def show_user_customers(current_user,): try : with connection.cursor() as cursor: public_id = current_user["public_id"] sql =( " SELECT customers_address,customers_city,customers_creator_id,customers_enddate,customers_name,customers_postcode,status_name FROM customers " " LEFT JOIN usercustomers on usercustomers.customers_public_id = customers.customers_public_id " " LEFT JOIN status on status.status_id = customers.status_id" " WHERE usercustomers.usercustomers_public_id = %s") cursor.execute(sql, (public_id,)) rv = cursor.fetchall() connection.commit() cursor.close() return jsonify( {"usersdetails" :rv} ) except Exception as e: print (e) return jsonify ({"Code" : "002" , "Message":"User Not found"})
def get_useravatar(public_id): if not public_id : return jsonify ({"status" : "error" , "message":"Missing PublicId"}) , 404 try : with connection.cursor() as cursor: sql = "SELECT usersdetails_avatar FROM usersdetails where usersdetails.user_public_id = %s" cursor.execute(sql, (public_id,)) rv = cursor.fetchone() avatar = rv['usersdetails_avatar'] if not rv: return jsonify ({"status" : "error" , "message":"Failed to find users"}) , 404 connection.commit() cursor.close() return redirect(url_for('static', filename='img/' + avatar), code=301) except Exception as e : return jsonify ({"Status" : "error" , "message":"Can't GET usersAvatar "}) , 500
def descjobdetails(current_user,public_job): try: public_id = current_user["public_id"] except: return jsonify({"Status": "Failed", "message": "Error DecodeId"}), 500 try: with connection.cursor() as cursor: sql = ("SELECT * from job LEFT JOIN status on status.status_id = job.status_id " " LEFT JOIN jobdetails on jobdetails.job_public_id = job.job_public_id WHERE job.job_public_id = %s" ) cursor.execute(sql, (public_job)) rv = cursor.fetchall() connection.commit() cursor.close() return jsonify({"Status": "success", "jobList": rv}), 200 except Exception as e: return jsonify({"Status": "Error", "projectList": e}), 500
def ListTeamproject(current_user, public_project): try: public_id = current_user.public_id username = current_user.username user_id = current_user.id except: return jsonify({"Status": "Failed", "message": "Error DecodeId"}), 200 with connection.cursor() as cursor: # Read a single record sql = " SELECT job.job_name , job.job_public_id , status.status_name , teamproject.teamproject_public_id ,job.job_created from job LEFT JOIN project_has_job ON job.job_public_id = project_has_job.job_public_id "\ " LEFT JOIN status on status.status_id = job.status_id "\ " LEFT JOIN teamproject on teamproject.teamproject_public_id = project_has_job.teamproject_public_id"\ " LEFT JOIN teamproject_has_user on teamproject_has_user.teamproject_public_id = teamproject.teamproject_public_id" \ " WHERE project_has_job.project_public_id = %s and teamproject_has_user.user_public_id = %s" cursor.execute(sql, (public_project, public_id)) rv = cursor.fetchall() connection.commit() cursor.close() return jsonify({"Status": "success", "projectList": rv}), 200
def UpdateAssetsisValid(current_user, assets_public, isvalid): try: public_id = current_user["public_id"] except: return jsonify({"Status": "Failed", "message": "Error DecodeId"}), 200 try: with connection.cursor() as cursor: sql = ( " update jobhasassets LEFT JOIN assets on assets.assets_id = jobhasassets.assets_id " " SET jobhasassets.assets_is_valid = %s WHERE assets.assets_public_id = %s " ) cursor.execute(sql, (isvalid, assets_public)) connection.commit() cursor.close() return jsonify({ "Status": "success", "updateAssets": "success" }), 200 except Exception as e: return jsonify({"Status": "Error", "projectList": e}), 500
def ListAssetsJob(assets_sn): try: with connection.cursor() as cursor: sql = ( " SELECT assets.assets_public_id , assets.assets_sn ,assetsbrand.assets_brand_name,assetscategories.assets_categories_name,assetsinsurance.assets_insurance_name,assetsseries.assets_series_name, jobhasassets.job_id ,jobhasassets.project_public_id,status.status_name " " , job.job_name , job.status_id , job_operator_id " " from assets LEFT JOIN customershasassets on assets.assets_id = customershasassets.assets_id " " LEFT JOIN status on customershasassets.status_id = status.status_id " " LEFT JOIN jobhasassets on jobhasassets.assets_id = assets.assets_id " " LEFT JOIN job on jobhasassets.job_id = job.job_id " " LEFT JOIN assetsbrand on assetsbrand.assets_brand_id = assets.assets_brand_id" " LEFT JOIN assetscategories on assetscategories.assets_categories_id = assets.assets_categories_id" " LEFT JOIN assetsinsurance on assetsinsurance.assets_insurance_id = assets.assets_insurance_id" " LEFT JOIN assetsseries on assetsseries.assets_series_id = assets.assets_series_id" " WHERE assets.assets_sn = %s ") cursor.execute(sql, (assets_sn, )) rv = cursor.fetchall() connection.commit() cursor.close() return jsonify({"Status": "success", "assets": rv}), 200 except Exception as e: return jsonify({"Status": "Error", "assets": e}), 500
def match(): if request.method == "POST": # gets user's code user = request.form.get("code") # connect to database cursor = connection.cursor() # Select all movies both users have liked cursor.execute( "SELECT * FROM top_250 WHERE id IN (SELECT movie_id FROM likes where preference = true AND user_id = %s) AND id IN (SELECT movie_id FROM likes where preference = true AND user_id = %s)", (session["user_id"], user)) rows = cursor.fetchall() row_count = cursor.rowcount if row_count == 0: return apology("no matches", 400) # Copy those movies over into a dictionary for display purposes matches = [] for i in range(row_count): match = {} match["title"] = rows[i][0] match["year"] = rows[i][1] match["image"] = rows[i][2] match["rating"] = rows[i][3] matches.append(match) return render_template("matched.html", matches=matches) else: friend_code = session["user_id"] return render_template("match.html", friend_code=friend_code)
def login_customers(): data = request.get_json() if not data or not data['username'] or not data['password']: return jsonify({ "Code" : "401_1" , 'Error' : "Username or password is empty " } ) , 401 with connection.cursor() as cursor: sql = "SELECT * FROM usercustomers where usercustomers.usercustomers_userkey = %s" cursor.execute(sql, (data['username'],)) rv = cursor.fetchone() if not rv: return jsonify({"Code": "401_2", 'Error': "Username is incorrect "}), 401 connection.commit() cursor.close() if not rv["usercustomers_userkey"] : return jsonify({"Code": "401_2", 'Error': "Username is incorrect "}), 401 try: if check_password_hash(rv["usercustomers_passkey"], data['password']): token = jwt.encode({'public_id' : rv["usercustomers_public_id"],'usercustomers_id' :rv["usercustomers_id"] ,'exp' : rv["usercustomers_exp"] , 'customers_public_id' : rv["customers_public_id"]}, Secret_key) return jsonify({'token':token.decode('UTF-8')}), 200 else: return jsonify({"Code": "401_2", 'Error': "Password is incorrect "}), 401 except Exception as e: return jsonify({"Code": "401_2", 'Error': "Error " +str(e)}), 500
def login(): data = request.get_json() if not data or not data['username'] or not data['password']: return jsonify({ "Code" : "401_1" , 'Error' : "Username or password is empty " } ) , 401 with connection.cursor() as cursor: sql = "SELECT * FROM user where user.username = %s" cursor.execute(sql, (data['username'],)) rv = cursor.fetchone() if not rv: return jsonify({"Code": "401_2", 'Error': "Username is incorrect "}), 401 connection.commit() cursor.close() if not rv["username"] : return jsonify({"Code": "401_2", 'Error': "Username is incorrect "}), 401 try: if check_password_hash(rv["password"], data['password']): token = jwt.encode({'public_id' : rv["public_id"],'user_id' :rv["id"] ,'exp' : datetime.datetime.utcnow() + datetime.timedelta(minutes=2440)}, Secret_key) return jsonify({'token':token.decode('UTF-8')}), 200 else: return jsonify({"Code": "401_2", 'Error': "Password is incorrect "}), 401 except Exception as e: return jsonify({"Code": "401_2", 'Error': "Error " +str(e)}), 500
def login(): """Log user in""" if request.method == "POST": # Ensure username was submitted if not request.form.get("username"): return apology("must provide username", 403) # Ensure password was submitted elif not request.form.get("password"): return apology("must provide password", 403) # Query database for username cursor = connection.cursor() cursor.execute("SELECT * FROM users WHERE username=%s", [request.form.get("username")]) # Ensure username exists and password is correct try: row = cursor.fetchone() if not check_password_hash(row[2], request.form.get("password")): return apology("invalid password", 403) except: return apology("invalid username", 403) # Stop acessing database cursor.close() # Remember which user has logged in session["user_id"] = row[0] # Redirect user to home page return redirect("/") # User reached route via GET (as by clicking a link or via redirect) else: return render_template("login.html")
def CountProjectByGroup(current_user): try: public_id = current_user["public_id"] except Exception as e: return jsonify({"Status": "Failed", "message": "Error DecodeId" + str(e)}), 500 try: with connection.cursor() as cursor: # Read a single record sql = ( " SELECT project.project_public_id , project_name , teamproject.teamproject_public_id , teamproject_name , teamproject_has_project.status_id ,status.status_name from project " " LEFT JOIN teamproject_has_project on teamproject_has_project.project_public_id = project.project_public_id " " LEFT JOIN teamproject on teamproject_has_project.teamproject_public_id = teamproject.teamproject_public_id" " LEFT JOIN teamproject_has_user on teamproject_has_user.teamproject_public_id = teamproject.teamproject_public_id " " LEFT JOIN project_has_job on project_has_job.project_public_id = project.project_public_id" " LEFT JOIN job on project_has_job.job_public_id = job.job_public_id" " LEFT JOIN status on status.status_id = teamproject_has_project.status_id" " WHERE teamproject_has_user.user_public_id = %s GROUP BY project_public_id" ) cursor.execute(sql, (public_id)) rv = cursor.fetchall() connection.commit() cursor.close() arr = dict() for r in rv: count = 0 val = [] project = r["status_name"] arr[project] = {} for a in rv: if project == a["status_name"]: count = count + 1 val.append(a) arr[project] = val # return jsonify(rv) return jsonify({"Status": "Success", "projectList:": arr}), 200 except Exception as e : return jsonify({"Status": "Failed", "message": "Error " +str( e)}), 500
def tearDown(self): connection.cursor()