def delete_user(data):#delete one user db=get_db() cursor=db.cursor() cursor.execute("SELECT * FROM Users WHERE personID = %s",(data,)) person=cursor.fetchone() if(not person): return {"error":"User not exists"},404 cursor.execute("DELETE FROM Passes WHERE personID=%s",(person[0],)) cursor.execute("SELECT * FROM Posts_ids WHERE personID = %s",(person[0],)) posts_of_person=cursor.fetchall() cursor.execute("DELETE FROM Attendee_list WHERE personID=%s",(person[0],)) cursor.execute("DELETE FROM Friends_of_user WHERE personID=%s",(person[0],)) cursor.execute("DELETE FROM Friends_of_user WHERE personID=%s",(person[0],)) cursor.execute("DELETE FROM Friends_of_user WHERE FriendID=%s",(person[0],)) for i in posts_of_person: cursor.execute("DELETE FROM Comments_list WHERE postID=%s",(i[0],)) if i[2]==0: cursor.execute("DELETE FROM Posts WHERE postID=%s",(i[0],)) elif i[2]==1: cursor.execute("DELETE FROM Events WHERE postID=%s",(i[0],)) elif i[2]==2: cursor.execute("DELETE FROM Items WHERE postID=%s",(i[0],)) cursor.execute("DELETE FROM Posts_ids WHERE personID=%s",(person[0],)) cursor.execute("DELETE FROM Users WHERE personID=%s",(person[0],)) cursor.close() db.commit() db.close() return {"succes":"user deleted"},200
def delete_post(data): #delete post db = get_db() cursor = db.cursor() try: cursor.execute("SELECT * FROM Posts_ids WHERE postID=%s", (data, )) post_root = cursor.fetchone() if not post_root: return {"error": "post not found"}, 404 if str(post_root[2]) == "0": cursor.execute("DELETE FROM Posts WHERE postID=%s", (post_root[0], )) elif str(post_root[2]) == "1": cursor.execute("DELETE FROM Events WHERE postID=%s", (post_root[0], )) elif str(post_root[2]) == "2": cursor.execute("DELETE FROM Items WHERE postID=%s", (post_root[0], )) cursor.execute("DELETE FROM Posts_ids WHERE postID=%s", (post_root[0], )) cursor.close() db.commit() db.close() return {"success": "Post deleted"}, 200 except: cursor.close() db.close() return {"error": "an error occured"}, 400
def update_one_user(data,payload):#update one user db=get_db() cursor=db.cursor() cursor.execute("SELECT * FROM Users WHERE personID = %s",(data,)) person=cursor.fetchone() if(not person): return {"error":"User not exists"},404 cursor.execute("SELECT hash_pass FROM Passes WHERE personID = %s",(data,)) hash_pass=cursor.fetchall() password=hash_pass[0][0] person_dict={ "personID":person[0], "fname":person[1], "lname":person[2], "email":person[3], "is_admin":person[4], "bio":person[5], "password":password } for key1 in person_dict: for key2 in payload: if key1==key2: person_dict[key1]=payload[key2] cursor.execute("UPDATE Users SET F_name=%(fname)s,L_name=%(lname)s,e_mail=%(email)s,is_admin=%(is_admin)s,bio=%(bio)s WHERE personID=%(personID)s",person_dict) cursor.execute("UPDATE Passes SET hash_pass=%(password)s WHERE personID=%(personID)s",person_dict) cursor.close() db.commit() db.close() return jsonify(person_dict)
def update_post(data): #update one post json_data = request.get_json() if (isinstance(json_data, str)): post = json.loads(json_data) elif (isinstance(json_data, dict)): post = json_data db = get_db() cursor = db.cursor() cursor.execute("SELECT * FROM Posts_ids WHERE postID = %s", (data, )) post_root = cursor.fetchone() if (not post_root): return {"error": "User not exists"}, 404 if str(post_root[2]) == "0": cursor.execute("SELECT * FROM Posts WHERE postID=%s", (post_root[0], )) post_data = cursor.fetchone() post_dict = { "postID": post_data[0], "payload": post_data[1], "is_news": post_data[2], "date": post_data[3] } elif str(post_root[2]) == "1": cursor.execute("SELECT * FROM Events WHERE postID=%s", (post_root[0], )) post_data = cursor.fetchone() post_dict = { "postID": post_data[0], "payload": post_data[1], "date": post_data[2] } elif str(post_root[2]) == "2": cursor.execute("SELECT * FROM Items WHERE postID=%s", (post_root[0], )) post_data = cursor.fetchone() post_dict = { "postID": post_data[0], "payload": post_data[1], "price": post_data[2], "date": post_data[3] } for key1 in post_dict: for key2 in post: if key1 == key2: post_dict[key1] = post[key2] if str(post_root[2]) == "0": cursor.execute( "UPDATE Posts SET payload=%(payload)s,is_news=%(is_news)s,publish_date=CURRENT_TIMESTAMP WHERE postID=%(postID)s", post_dict) elif str(post_root[2]) == "1": cursor.execute( "UPDATE Events SET payload=%(payload)s,publish_date=CURRENT_TIMESTAMP WHERE postID=%(postID)s", post_dict) elif str(post_root[2]) == "2": cursor.execute( "UPDATE Items SET payload=%(payload)s,price=%(price)s,publish_date=CURRENT_TIMESTAMP WHERE postID=%(postID)s", post_dict) cursor.close() db.commit() db.close() return jsonify(post_dict)
def users_all(): #read all users db=get_db() cursor=db.cursor() cursor.execute('SELECT * FROM Users;') users=cursor.fetchall() user_api=[] for i in users: user_api.append({"id":i[0],"fname":i[1],"lname":i[2],"email":i[3],"is_admin":i[4],"bio":i[5]}) return jsonify(user_api)
def auth_check(personID):#auth check db=get_db() cursor=db.cursor() cursor.execute("SELECT hash_pass FROM Passes WHERE personID=%s",(personID,)) user_pass=cursor.fetchone() cursor.close() db.close() return {"hash_pass":user_pass},200
def delete_friendship(data): db=get_db() cursor=db.cursor() cursor.execute("SELECT * FROM Friends_of_user WHERE FriendshipID=%s",(data,)) response=cursor.fetchone() cursor.execute("DELETE FROM Friends_of_user WHERE personID=%s AND FriendID=%s",(response[0],response[1],)) cursor.execute("DELETE FROM Friends_of_user WHERE personID=%s AND FriendID=%s",(response[1],response[0],)) cursor.close() db.commit() db.close() return jsonify({"success":"friendship is destroyed"})
def accept_friendship(data): db=get_db() cursor=db.cursor() cursor.execute("SELECT * FROM Friends_of_user WHERE FriendshipID=%s",(data,)) response=cursor.fetchone() cursor.execute("UPDATE Friends_of_user SET accepted=TRUE WHERE personID=%s AND FriendID=%s",(response[0],response[1],)) cursor.execute("UPDATE Friends_of_user SET accepted=TRUE WHERE personID=%s AND FriendID=%s",(response[1],response[0],)) cursor.close() db.commit() db.close() return jsonify({"success":"friends are shipped"})
def friendship_requests(data): db=get_db() cursor=db.cursor() cursor.execute("SELECT * FROM Friends_of_user WHERE personID=%s AND accepted=FALSE",(data,)) response=cursor.fetchall() result=[] for i in response: cursor.execute("SELECT * FROM Users WHERE personID=%s",(i[1],)) friend_data=cursor.fetchone() result.append({"FriendshipID":i[2],"personID":i[0],"FriendID":i[1],"accepted":i[3],"friend_data":{"personID":friend_data[0],"fname":friend_data[1],"lname":friend_data[2],"email":friend_data[3]}}) cursor.close() db.close() return jsonify(result)
def user_one(data):#read one user db=get_db() cursor=db.cursor() if data.find("@")==-1: cursor.execute("SELECT * FROM Users WHERE personID = %s;",(data,)) else: cursor.execute("SELECT * FROM Users WHERE e_mail = %s;",(data,)) that_user=cursor.fetchone() if that_user: user_api={"personID":that_user[0],"fname":that_user[1],"lname":that_user[2],"email":that_user[3],"is_admin":that_user[4],"bio":that_user[5]} else: cursor.close() db.close() return {"error":"Not Found"}, 404 cursor.close() db.close() return jsonify(user_api)
def get_one_post(data): #get one post db = get_db() cursor = db.cursor() cursor.execute("SELECT * FROM Posts WHERE postID=%s", (data, )) Posts = cursor.fetchone() cursor.execute("SELECT * FROM Events WHERE postID=%s", (data, )) Events = cursor.fetchone() cursor.execute("SELECT * FROM Items WHERE postID=%s", (data, )) Items = cursor.fetchone() cursor.execute("SELECT personID FROM Posts_ids WHERE postID=%s", (data, )) personID = cursor.fetchone() cursor.execute("SELECT F_name,L_name FROM Users WHERE personID=%s", (personID[0], )) name = cursor.fetchone() res_dict = {} if (Posts): res_dict = { "name": name[0] + " " + name[1], "postID": Posts[0], "payload": Posts[1], "is_news": Posts[2], "publish_date": Posts[3] } elif (Events): res_dict = { "name": name[0] + " " + name[1], "postID": Events[0], "payload": Events[1], "publish_date": Events[2] } elif (Items): res_dict = { "name": name[0] + " " + name[1], "postID": Items[0], "payload": Items[1], "price": Items[2], "publish_date": Items[3] } else: cursor.close() db.close() return {"error": "no post found"}, 404 cursor.close() db.close() return jsonify(res_dict)
def send_friendship(data): db=get_db() cursor=db.cursor() json_data=request.get_json() if(isinstance(json_data,str)): friend_req=json.loads(json_data) elif(isinstance(json_data,dict)): friend_req=json_data friend_req.update({"personID":data}) cursor.execute("INSERT INTO Friends_of_user (personID,FriendID,accepted) VALUES (%(personID)s,%(FriendID)s,%(accepted)s)",friend_req) cursor.execute("INSERT INTO Friends_of_user (personID,FriendID,accepted) VALUES (%(FriendID)s,%(personID)s,%(accepted)s)",friend_req) cursor.execute("SELECT FriendshipID FROM Friends_of_user WHERE personID=%(personID)s AND FriendID=%(FriendID)s",friend_req) newly_inserted=cursor.fetchone() friend_req.update({"FriendshipID":newly_inserted[0]}) cursor.close() db.commit() db.close() return jsonify(friend_req)
def insert_one_user(data):#create one user db=get_db() cursor=db.cursor() cursor.execute("SELECT COUNT(*) FROM Users WHERE e_mail = %(email)s",data) check=cursor.fetchone() if(check[0]>0): return {"error":"User exists"},409 cursor.execute("INSERT INTO Users (F_name,L_name,e_mail,is_admin,bio) VALUES (%(fname)s,%(lname)s,%(email)s,%(is_admin)s,%(bio)s)",data) cursor.execute("SELECT personID FROM Users WHERE e_mail = %(email)s",data) that_user_id=cursor.fetchone() hash_pass_dict={ 'password':data['password'], 'personID':that_user_id[0] } cursor.execute("INSERT INTO Passes (hash_pass,personID) VALUES (%(password)s,%(personID)s)",hash_pass_dict) data.update({"id":that_user_id[0]}) cursor.close() db.commit() db.close() return data,201
def new_post(): #add post json_data = request.get_json() if (isinstance(json_data, str)): data = json.loads(json_data) elif (isinstance(json_data, dict)): data = json_data db = get_db() cursor = db.cursor() try: cursor.execute( "INSERT INTO Posts_ids (personID,iem_type) VALUES (%(personID)s,%(type)s)", data) cursor.execute( "SELECT MAX(postID) FROM Posts_ids WHERE personID=%(personID)s", data) postID = cursor.fetchone() data.update({"postID": postID[0]}) if data["type"] == "0": cursor.execute( "INSERT INTO Posts (postID,payload,is_news) VALUES (%(postID)s,%(payload)s,%(is_news)s)", data) elif data["type"] == "1": cursor.execute( "INSERT INTO Events (postID,payload) VALUES (%(postID)s,%(payload)s)", data) elif data["type"] == "2": cursor.execute( "INSERT INTO Items (postID,payload,price) VALUES (%(postID)s,%(payload)s,%(price)s)", data) cursor.close() db.commit() except: cursor.close() db.close() return {"error": "error occured"}, 400 cursor.close() db.close() return data, 200
def get_all_posts(): #get all of the posts db = get_db() cursor = db.cursor() cursor.execute("SELECT * FROM Posts") Posts = cursor.fetchall() cursor.execute("SELECT * FROM Events") Events = cursor.fetchall() cursor.execute("SELECT * FROM Items") Items = cursor.fetchall() all_posts = {"posts": [], "events": [], "items": []} for i in Posts: cursor.execute("SELECT personID FROM Posts_ids WHERE postID=%s", (i[0], )) personID = cursor.fetchone() cursor.execute("SELECT * FROM Users WHERE personID=%s", (personID[0], )) user = cursor.fetchone() post = { "user_info": { "personID": user[0], "Name": user[1] + " " + user[2], "email": user[3] }, "postID": i[0], "payload": i[1], "is_news": i[2], "publish_date": i[3] } all_posts["posts"].append(post) for i in Events: cursor.execute("SELECT personID FROM Posts_ids WHERE postID=%s", (i[0], )) personID = cursor.fetchone() cursor.execute("SELECT * FROM Users WHERE personID=%s", (personID[0], )) user = cursor.fetchone() event = { "user_info": { "personID": user[0], "Name": user[1] + " " + user[2], "email": user[3] }, "postID": i[0], "payload": i[1], "publish_date": i[2] } all_posts["events"].append(event) for i in Items: cursor.execute("SELECT personID FROM Posts_ids WHERE postID=%s", (i[0], )) personID = cursor.fetchone() cursor.execute("SELECT * FROM Users WHERE personID=%s", (personID[0], )) user = cursor.fetchone() item = { "user_info": { "personID": user[0], "Name": user[1] + " " + user[2], "email": user[3] }, "postID": i[0], "payload": i[1], "price": i[2], "publish_date": i[3] } all_posts["items"].append(item) cursor.close() db.close() return jsonify(all_posts)
def get_friends_posts(data): #get all posts from users friends db = get_db() cursor = db.cursor() cursor.execute( "SELECT tb3.* FROM Posts tb3 INNER JOIN ( SELECT tb1.postID FROM Posts_ids tb1 INNER JOIN Friends_of_user tb2 ON tb1.personID=tb2.FriendID AND tb2.personID=%s AND tb2.accepted=TRUE) tb4 ON tb4.postID=tb3.postID", (data, )) posts = cursor.fetchall() cursor.execute( "SELECT tb5.* FROM Events tb5 INNER JOIN ( SELECT tb1.postID FROM Posts_ids tb1 INNER JOIN Friends_of_user tb2 ON tb1.personID=tb2.FriendID AND tb2.personID=%s AND tb2.accepted=TRUE) tb6 ON tb5.postID=tb6.postID", (data, )) events = cursor.fetchall() cursor.execute( "SELECT tb7.* FROM Items tb7 INNER JOIN ( SELECT tb1.postID FROM Posts_ids tb1 INNER JOIN Friends_of_user tb2 ON tb1.personID=tb2.FriendID AND tb2.personID=%s AND tb2.accepted=TRUE) tb8 ON tb7.postId=tb8.postID", (data, )) items = cursor.fetchall() all_posts = { "posts": [], "events": [], "items": [], } for i in posts: cursor.execute("SELECT personID FROM Posts_ids WHERE postID=%s", (i[0], )) personID = cursor.fetchone() cursor.execute("SELECT * FROM Users WHERE personID=%s", (personID[0], )) user = cursor.fetchone() post = { "user_info": { "personID": user[0], "Name": user[1] + " " + user[2], "email": user[3] }, "postID": i[0], "payload": i[1], "is_news": i[2], "publish_date": i[3] } all_posts["posts"].append(post) for i in events: cursor.execute("SELECT personID FROM Posts_ids WHERE postID=%s", (i[0], )) personID = cursor.fetchone() cursor.execute("SELECT * FROM Users WHERE personID=%s", (personID[0], )) user = cursor.fetchone() event = { "user_info": { "personID": user[0], "Name": user[1] + " " + user[2], "email": user[3] }, "postID": i[0], "payload": i[1], "publish_date": i[2] } all_posts["events"].append(event) for i in items: cursor.execute("SELECT personID FROM Posts_ids WHERE postID=%s", (i[0], )) personID = cursor.fetchone() cursor.execute("SELECT * FROM Users WHERE personID=%s", (personID[0], )) user = cursor.fetchone() item = { "user_info": { "personID": user[0], "Name": user[1] + " " + user[2], "email": user[3] }, "postID": i[0], "payload": i[1], "price": i[2], "publish_date": i[3] } all_posts["items"].append(item) cursor.close() db.close() return jsonify(all_posts)