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()
