def get_post_tribe(userid): db_connection = conn.create_connection() db_cursor = db_connection.cursor() #sql = 'select * from post where userid=%s', (userid,); db_cursor.execute( 'select * from post where userid in (select userid from users where tribe_id in (select tribe_id from users where userid= %s))', (userid, )) rv = db_cursor.fetchall() db_cursor.close() db_connection.close() if (rv): payload = [] content = {} for result in rv: content = { 'postid': result[0], 'posttext': result[2], 'imageurl': result[3], 'timestamp': result[4] } payload.append(content) content = {} result_json = jsonify(payload) else: result_json = "don't have post yet" return result_json
def add_like(postid, userid, timestamp): db_connection = conn.create_connection() db_cursor = db_connection.cursor() db_cursor.execute('select * from likes where userid = %s and postid= %s', (userid, postid)) result = db_cursor.fetchone() print(result) # status 0 means likes, 1 means disliked if (result): if (result[3] == 0): db_cursor.execute( 'update likes set active_likes = %s where userid = %s and postid= %s', (1, userid, postid)) db_connection.commit() else: db_cursor.execute( 'update likes set active_likes = %s where userid = %s and postid= %s', (0, userid, postid)) db_connection.commit() msg = 'like updated' else: sql = 'INSERT INTO likes (postid, userid,active_likes) VALUES(%s,%s,%s)' val = (postid, userid, 0) db_cursor.execute(sql, val) db_connection.commit() msg = 'like added' return msg
def login(username, password): # Creating Db connection and cursor db_connection = conn.create_connection() db_cursor = db_connection.cursor() #db_cursor.execute('SELECT * FROM users WHERE userid = %s AND password = %s', (username, password)) db_cursor.execute('SELECT users.userid, users.name, users.email,users.contact,users.address, tribe.tribename, mentor.mentorname FROM users INNER JOIN tribe ON users.tribe_id=tribe.tribeid INNER JOIN mentor ON users.mentor_id = mentor.mentorid where users.userid = %s and users.password = %s', (username, password)) #db_cursor.execute('SELECT users.userid, users.name, users.email,users.contact,users.address, tribe.tribename, mentor.mentorname FROM users INNER JOIN tribe ON users.tribe_id=tribe.tribeid INNER JOIN mentor ON users.mentor_id = mentor.mentorid where users.userid='shiva123' and users.password='******') # Fetch one record and return result account = db_cursor.fetchone() # fetching data # If account exists in accounts table in out database if account: # Create session data, we can access this data in other routes session['loggedin'] = True session['userid'] = account[0] session['name'] = account[1] # Close Db connection and cursor db_cursor.close() db_connection.close() #print(account) response = {"userid":account[0],"name":account[1],"email":account[2],"contact":account[3],"address":account[4],"tribename":account[5],"mentorname":account[6]} else: # Account doesnt exist or username/password incorrect response = 'Incorrect username/password!' return response # returnd data
def add_comment(postid, commenttext, timestamp): db_connection = conn.create_connection() db_cursor = db_connection.cursor() sql = 'INSERT INTO comment (commenttext, timestamp, postid) VALUES(%s,%s,%s)' val = (commenttext, timestamp, postid) db_cursor.execute(sql, val) db_connection.commit() return "Comment Added"
def add_post(userid, text, timestamp): db_connection = conn.create_connection() db_cursor = db_connection.cursor() sql = 'INSERT INTO post (userid,text,imageurl,timestamp) VALUES(%s,%s,%s,%s)' val = (userid, text, '', timestamp) db_cursor.execute(sql, val) db_connection.commit() return "Post Added"
def get_course_assignment(coursename): # Creating Db connection and cursor db_connection = conn.create_connection() db_cursor = db_connection.cursor() db_cursor.execute('select assesment.assesmentid, assesment.assesment_name, assesment.assesment_score, course.coursename from assesment inner join course on course.courseid = assesment.course_id where course.coursename=%s',(coursename,)) # Fetch all record and return result result = db_cursor.fetchall() # Close Db connection and cursor db_cursor.close() db_connection.close() return result
def get_course_progress(username): # Creating Db connection and cursor db_connection = conn.create_connection() db_cursor = db_connection.cursor() db_cursor.execute('SELECT COUNT(assesmentid), course.coursename FROM user_course_assesment INNER JOIN course ON user_course_assesment.courseid=course.courseid where userid = %s and assesment_state = %s GROUP BY user_course_assesment.userid,user_course_assesment.courseid',(username,'finished')) # Fetch all record and return result result = db_cursor.fetchall() # Close Db connection and cursor db_cursor.close() db_connection.close() return result
def register(userid, password, name, email, address, tribe_id, mentor_id): # Creating Db connection and cursor db_connection = conn.create_connection() db_cursor = db_connection.cursor() sql = 'INSERT INTO users (userid,password,name,email,address,tribe_id,mentor_id) VALUES(%s,%s,%s,%s,%s,%s,%s)' val = (userid, password, name, email, address, tribe_id, mentor_id) db_cursor.execute(sql, val) db_connection.commit() # Close Db connection and cursor db_cursor.close() db_connection.close() return "Registration Successful"
def get_alllikes(postid): db_connection = conn.create_connection() db_cursor = db_connection.cursor() #sql = 'select * from post where userid=%s', (userid,); db_cursor.execute( 'select count(likeid) from likes where postid=%s and active_likes=%s group by(postid);', (postid, 0)) result = db_cursor.fetchone() if (result): all_likes = {'alllikes': result[0]} else: all_likes = 'post have 0 likes' db_cursor.close() db_connection.close() print(all_likes) return all_likes
def get_points(username): # Creating Db connection and cursor db_connection = conn.create_connection() db_cursor = db_connection.cursor() db_cursor.execute('SELECT * FROM points WHERE userid = %s', (username, )) # Fetch one record and return result result = db_cursor.fetchone() # Close Db connection and cursor db_cursor.close() db_connection.close() if (result): result_json = { "wisdompoints": result[1], "connectionpoints": result[2] } else: result_json = "User don't have points" return result_json
def get_tedencies(username): # Creating Db connection and cursor db_connection = conn.create_connection() db_cursor = db_connection.cursor() db_cursor.execute('SELECT * FROM tendencies WHERE userid = %s', (username, )) # Fetch one record and return result result = db_cursor.fetchone() # Close Db connection and cursor db_cursor.close() db_connection.close() if (result): result_json = { "tendencie1": result[1], "tendencie2": result[2], "tendencie3": result[3] } else: result_json = "User don't have tendencies" return result_json
def get_all_course(): # Creating Db connection and cursor db_connection = conn.create_connection() db_cursor = db_connection.cursor() db_cursor.execute('SELECT * FROM course') # Fetch all record and append these into a json and return result rv = db_cursor.fetchall() # Close Db connection and cursor db_cursor.close() db_connection.close() if (rv): payload = [] content = {} for result in rv: content = {'courseid': result[0], 'coursename': result[1]} payload.append(content) content = {} result_json = jsonify(payload) else: result_json = "don't have any course" return result_json
def get_commnets(postid): db_connection = conn.create_connection() db_cursor = db_connection.cursor() #sql = 'select * from post where userid=%s', (userid,); db_cursor.execute('select * from comment where postid = %s', (postid, )) rv = db_cursor.fetchall() db_cursor.close() db_connection.close() if (rv): payload = [] content = {} for result in rv: content = { 'commentid': result[0], 'commenttext': result[1], 'timestamp': result[2] } payload.append(content) content = {} result_json = jsonify(payload) else: result_json = "no comment on this post" return result_json