def get_genre_id(genre): #Get genre id given genre cur = get_cursor() cur.execute('''select g_id from Genre where STRCMP(name,'{0}') = 0'''.format(genre)) g_id = cur.fetchall() if g_id : # assuming a None is returned if the genre is not present return g_id[0]['g_id'] return None
def sendRequestDB(u_id_s, u_id_r): cur = get_cursor() cur.execute( '''INSERT INTO FriendRequest(u_id_s,u_id_r) VALUES({0},{1})'''.format( u_id_s, u_id_r)) get_db().connection.commit() return 'Friend Request Sent'
def get_book_rating(g_id): cur=get_cursor() cur.execute('''select Book.b_id, rating, title from Book inner join GenreBooks on Book.b_id = GenreBooks.b_id where GenreBooks.g_id = {0}'''.format(g_id)) return cur.fetchall()
def get_Genre_By_Genre_Id(g_id): #Get genre given genre id cur = get_cursor() cur.execute('''select name from Genre where g_id = {0}'''.format(g_id)) genre = cur.fetchall() if genre : # assuming a None is returned if the genre is not present return genre[0]['name'] return None
def selectGenresDB(): #Database operations cur = get_cursor() cur.execute('''SELECT name FROM Genre''') genres = cur.fetchall() genresSet = [] for ele in genres: genresSet.append(ele['name']) return genresSet #return a list of genres
def getBookIdsByGenreId(g_id): #get a list of book ids with g_id = something cur = get_cursor() cur.execute('''select b_id from GenreBooks where g_id = {0}'''.format(g_id)) #get book ids of books beloning to a books = cur.fetchall() # particular genre booksList = [] for b in books: booksList.append(b['b_id']) return booksList
def get_details(username): cur = get_cursor() # Querying result = cur.execute("SELECT u_id, password FROM User WHERE username = %s", [username]) data = cur.fetchone() cur.close() return data # contains u_id and encrypted password..
def selectGenresIdDB(): #Database operations cur = get_cursor() cur.execute('''SELECT g_id FROM Genre''') genres = cur.fetchall() print(type(genres)) genresSet = [] for ele in genres: genresSet.append(ele['g_id']) return genresSet
def cancelRequestDB(u_id1, u_id2): cur = get_cursor() if FriendRequestExists(u_id1, u_id2) is False: return "The friend request does not exist" cur.execute( ''' delete from FriendRequest where u_id_s = {0} AND u_id_r = {1} '''. format(u_id1, u_id2)) get_db().connection.commit() return "Friend Request Cancelled"
def email_exists(email): cur = get_cursor() # Querying result = cur.execute('''SELECT u_id from User WHERE email = %s''', [email]) cur.close() if result > 0: return True else: return False
def get_details(u_id): cur = get_cursor() #Querying... cur.execute( '''SELECT name, username, email, date_created FROM User WHERE u_id = %s ''', [u_id]) result = cur.fetchall() cur.close() return jsonify(result)
def FriendRequestExists(u_id_1, u_id_2): cur = get_cursor() cur.execute( '''SELECT count(*) FROM FriendRequest WHERE (u_id_s = {1} AND u_id_r = {0}) OR (u_id_s = {0} AND u_id_r = {1})''' .format(u_id_1, u_id_2)) count = cur.fetchone()['count(*)'] print(type(u_id_2)) if count is 0: return False return True
def get_all(self): cur = get_cursor() sql = "SELECT tzid FROM timezones_world WHERE tzid != 'uninhabited';" cur.execute(sql) rows = cur.fetchall() result = [] for row in rows: result.append(row[0]) cur.close() return result
def selectAuthorDetails(result,booklist): #get author details for all the b_ids of a particular g_id cur = get_cursor() format_strings = ','.join(['%s'] * len(booklist)) cur.execute("SELECT name FROM Author WHERE b_id in (%s)" % format_strings, tuple(booklist)) vr = cur.fetchall() if vr: for i in range(len(vr)): result['response'][i]['Author']['name'] = vr[i]['name']
def book_exists(book_name): cur = get_cursor() #Querying... cur.execute('''SELECT b_id FROM Book WHERE title = %s''', [book_name]) result = cur.fetchone() cur.close() if result is None: return False return result['b_id']
def username_exists(username): cur = get_cursor() # Querying result = cur.execute('''SELECT u_id from User WHERE username = %s''', [username]) cur.close() if result > 0: return True else: return False
def password_matches(username, old_password): cur = get_cursor() cur.execute('''SELECT password FROM User WHERE username = %s ''', [username]) password = cur.fetchone()['password'] cur.close() if sha256_crypt.verify(old_password, password): # decrypt and compare..... return True return False
def book_exists_for_user(u_id, b_id): cur = get_cursor() # Querying the DB cur.execute('''SELECT b_id FROM BookList WHERE u_id = %s and b_id = %s''', (u_id, b_id)) books = cur.fetchone() cur.close() # Close connection.. if books > 0: # If book exists return True.. return True else: return False
def get_comments_on_book(b_id): cur = get_cursor() #Querying... cur.execute( '''SELECT Comments.c_id, Comments.title, Comments.comment_date, User.username, Comments.c_id, Comments.u_id FROM Comments LEFT JOIN User ON Comments.u_id = User.u_id WHERE Comments.b_id = %s ''', [b_id]) result = cur.fetchall() cur.close() return jsonify(result)
def get_user_comments(u_id): cur = get_cursor() #Querying... cur.execute( '''SELECT Comments.title, Comments.c_id, Comments.comment_date, Book.title as book FROM Comments LEFT JOIN Book ON Book.b_id = Comments.b_id WHERE u_id = %s ''', [u_id]) result = cur.fetchall() cur.close() print(result) return jsonify(result)
def selectBookDetails(result,booklist): #get book details for all the b_ids of a particular g_id cur = get_cursor() format_strings = ','.join(['%s'] * len(booklist)) cur.execute("SELECT title, rating, no_ratings, cover FROM Book WHERE b_id in (%s)" % format_strings, tuple(booklist)) vr = cur.fetchall() if vr: book_q = 'title, rating, no_ratings, cover'.split(', ') for i in range(len(vr)): result['response'].append({ 'Book':{},'Author':{} }) for j in range(len(book_q)): result['response'][i]['Book'][book_q[j]] = vr[i][book_q[j]]
def getbooks(u_id, status, returnjson): cur = get_cursor() # Querying.. cur.execute( '''SELECT Book.title, Book.b_id FROM BookList LEFT JOIN Book ON Book.b_id = BookList.b_id WHERE u_id = %s AND status = %s ''', (u_id, status)) # JOIN between Book and BookList gives the name of books for the user with a status... books = cur.fetchall() cur.close() if returnjson: return jsonify(books) return books
def get_timezone_utc(self, lon, lat): cur = get_cursor() sql = ''' SELECT name FROM timezones_utc WHERE ST_Contains(geom, ST_MakePoint(''' + str( lon) + ''', ''' + str(lat) + ''')) LIMIT 1 OFFSET 0; ''' cur.execute(sql) rows = cur.fetchall() result = {} for row in rows: result["timezone"] = "UTC" + row[0] cur.close() return result
def insertGenreDB(genre): #Database operations cur = get_cursor() cur.execute('''insert into Genre(name) values(%s)''',[genre]) get_db().connection.commit()
def userExists(u_id): cur = get_cursor() cur.execute('''select u_id from User where u_id = {0}'''.format(u_id)) if not cur.fetchall(): return False return True
def getAllUsers(): cur = get_cursor() cur.execute('''select u_id,name from User''') return cur.fetchall()
def viewFriendsDB(u_id): cur = get_cursor() cur.execute( '''select u_id,name from User where u_id in ( select u_id_1 from Friends where u_id_2 = {0} UNION select u_id_2 from Friends where u_id_1 = {0})'''.format(u_id)) return cur.fetchall()
def viewRequestDB(u_id): cur = get_cursor() cur.execute( '''select u_id,name from User where u_id in ( select u_id_s from FriendRequest where u_id_r = {0})''' .format(u_id)) return cur.fetchall()