def db_get_book(id):
conn = get_database_connection()
print("Encoding for this connection is", conn.encoding)
curs = conn.cursor()
dictionary = {}
dictionary['status'] = 200
try:
curs.execute("SELECT * FROM books where id='%s'" % id)
for row in curs.fetchall():
print(row)
dictionary['id'] = row[0]
dictionary['title'] = row[1]
dictionary['author'] = row[2]
dictionary['genre'] = row[3]
dictionary['description'] = row[4]
dictionary['coverUrl'] = row[5]
except psycopg2.Error as e:
print('err', e)
dictionary = {}
dictionary['status'] = 500
pass
curs.close()
conn.commit()
conn.close()
return build_response(dictionary, None)
def db_delete_favorite():
if (request.data == b''):
return Response(status=400)
dataDict = json.loads(request.data)
payload = is_authorized()
if (payload != None):
dictionary = find_user_by_email(payload)
if (dictionary['status'] == 200):
user_id = dictionary['id']
book_id = dataDict['bookId']
conn = get_database_connection()
print("Encoding for this connection is", conn.encoding)
curs = conn.cursor()
response = Response("true", status=200)
try:
curs.execute(
"delete from favorites where book_id={book_id} and user_id={user_id} returning *"
.format(book_id=book_id, user_id=user_id))
for row in curs.fetchall():
print('delete: ', row)
except psycopg2.Error as e:
print('delete error', e)
response = Response("false", status=200)
pass
curs.close()
conn.commit()
conn.close()
return response
else:
return build_response(dictionary, None)
else:
return Response(status=401)
def db_delete_book(id):
dictionary = {}
conn = get_database_connection()
curs = conn.cursor()
try:
curs.execute("DELETE FROM books where id='%s'" % id)
dictionary['status'] = 200
except psycopg2.Error as e:
print('err', e)
dictionary['status'] = 400
pass
conn.commit()
curs.close()
conn.close()
return build_response(dictionary, None)
def db_update_book(id):
if (request.data == b''):
return Response(status=401)
dataDict = json.loads(request.data)
print("update", dataDict)
sqlString = 'update books set '
if (dataDict['title'] != None):
sqlString += 'title="' + dataDict['title'] + '"'
if (dataDict['author'] != None):
if (len(sqlString) > 0):
sqlString += ', '
sqlString += 'author="' + dataDict['author'] + '"'
if (dataDict['genre'] != None):
if (len(sqlString) > 0):
sqlString += ', '
sqlString += 'genre="' + dataDict['genre'] + '"'
if (dataDict['description'] != None):
if (len(sqlString) > 0):
sqlString += ', '
sqlString += 'description="' + dataDict['description'] + '"'
if (dataDict['coverUrl'] != None):
if (len(sqlString) > 0):
sqlString += ', '
sqlString += 'cover_url="' + dataDict['coverUrl'] + '"'
sqlString += ' where id = ' + id
print(sqlString)
conn = get_database_connection()
curs = conn.cursor()
try:
curs.execute(sqlString)
dictionary = {}
for row in curs.fetchall():
print(row)
dictionary['id'] = row[0]
dictionary['title'] = row[1]
dictionary['author'] = row[2]
dictionary['genre'] = row[3]
dictionary['description'] = row[4]
dictionary['coverUrl'] = row[5]
except psycopg2.Error as e:
print('err', e)
dictionary = {}
dictionary['status'] = 400
pass
conn.commit()
curs.close()
conn.close()
return build_response(dictionary, None)
def db_add_favorite():
if (request.data == b''):
return Response(status=400)
dataDict = json.loads(request.data)
payload = is_authorized()
if (payload != None):
dictionary = find_user_by_email(payload)
if (dictionary['status'] == 200):
user_id = dictionary['id']
book_id = dataDict['bookId']
print('user_id: ', user_id)
print('book_id: ', book_id)
conn = get_database_connection()
print("Encoding for this connection is", conn.encoding)
curs = conn.cursor()
response = Response("true", status=200)
try:
sqlString = "insert into favorites (book_id, user_id) values({book_id}, {user_id}) returning *".format(
book_id=book_id, user_id=user_id)
print('sqlString', sqlString)
curs.execute(sqlString)
if (curs.rowcount > 0):
for row in curs.fetchall():
print('insert: ', row)
dictionary = {}
dictionary['status'] = 200
dictionary['id'] = row[0]
dictionary['bookId'] = row[1]
dictionary['userId'] = row[2]
print('dictionary: ', dictionary)
response = build_response(dictionary, None)
else:
print('error - unable to insert')
response = Response("false", status=200)
except psycopg2.Error as e:
print('error', e)
response = Response("false", status=200)
pass
curs.close()
conn.commit()
conn.close()
return response
else:
return build_response(dictionary, None)
else:
return Response(status=401)
def db_list_books():
conn = get_database_connection()
print("Encoding for this connection is", conn.encoding)
curs = conn.cursor()
print("Extracting the rows ...")
curs.execute("SELECT * FROM books order by title")
data = list()
for row in curs.fetchall():
# print(row)
dictionary = {}
dictionary['id'] = row[0]
dictionary['title'] = row[1]
dictionary['author'] = row[2]
dictionary['genre'] = row[3]
dictionary['description'] = row[4]
dictionary['coverUrl'] = row[5]
data.insert(0, dictionary)
# dict(id=row[0], title=row[1], author=row[2], genre=row[3]))
# data.insert(0, row)
return build_array_response(data)
def db_create_user():
if (request.data == b''):
return Response(status=401)
dataDict = json.loads(request.data)
email = dataDict['email']
password = dataDict['password']
first_name = dataDict['firstName']
last_name = dataDict['lastName']
if (email == None):
abort(401)
if (password == None):
abort(401)
if (first_name == None):
abort(401)
if (last_name == None):
abort(401)
hashed_password = get_hashed_password(password)
dictionary = {}
conn = get_database_connection()
curs = conn.cursor()
try:
curs.execute(
"""
insert into users (first_name, last_name, email, hashed_password)
values (%s, %s, %s, %s);
""", (first_name, last_name, email, hashed_password))
dictionary['status'] = 200
dictionary['firstName'] = first_name
dictionary['lastName'] = last_name
dictionary['email'] = email
dictionary['hashedPassword'] = hashed_password
except psycopg2.Error as e:
print('err', e)
dictionary = {}
dictionary['status'] = 400
pass
conn.commit()
curs.close()
conn.close()
return build_response(dictionary, email)
def db_check_favorite():
payload = is_authorized()
if (payload != None):
dictionary = find_user_by_email(payload)
if (dictionary['status'] == 200):
user_id = dictionary['id']
book_id = request.args.get('bookId')
conn = get_database_connection()
print("Encoding for this connection is ", conn.encoding)
curs = conn.cursor()
try:
curs.execute(
"select * from favorites where book_id={book_id} and user_id={user_id}"
.format(book_id=book_id, user_id=user_id))
if (curs.rowcount > 0):
for row in curs.fetchall():
print('check: ', row)
dictionary = {}
dictionary['status'] = 200
dictionary['id'] = row[0]
dictionary['bookId'] = row[1]
dictionary['userId'] = row[2]
print('dictionary: ', dictionary)
response = build_response(dictionary, None)
else:
response = Response("false", status=200)
except psycopg2.Error as e:
print('not foun: ', e)
response = Response("false", status=200)
pass
curs.close()
conn.commit()
conn.close()
return response
else:
return build_array_response(dictionary, None)
else:
return Response(status=401)
def db_list_favorites():
payload = is_authorized()
if (payload != None):
dictionary = find_user_by_email(payload)
if (dictionary['status'] == 200):
conn = get_database_connection()
print("Encoding for this connection is", conn.encoding)
user_id = dictionary['id']
curs = conn.cursor()
print("Extracting the rows ...")
sqlString = db_get_favorite_sql_string(user_id)
curs.execute(sqlString)
response = Response(json.dumps({}), status=200)
if (curs.rowcount > 0):
data = list()
for row in curs.fetchall():
# print(row)
dictionary = {}
dictionary['author'] = row[0]
dictionary['coverUrl'] = row[1]
dictionary['description'] = row[2]
dictionary['genre'] = row[3]
dictionary['title'] = row[4]
dictionary['bookId'] = row[5]
dictionary['userId'] = row[6]
dictionary['id'] = row[7]
data.insert(0, dictionary)
# dict(id=row[0], title=row[1], author=row[2], genre=row[3]))
# data.insert(0, row)
response = build_array_response(data)
curs.close()
conn.commit()
conn.close()
return response
else:
return build_response(dictionary, None)
else:
return Response(status=401)
def db_create_book():
if (request.data == b''):
return Response(status=401)
dataDict = json.loads(request.data)
if (dataDict['title'] == None):
return Response(status=401, message='Title must not be blank')
if (dataDict['author'] == None):
return Response(status=401, message='Author must not be blank')
if (dataDict['genre'] == None):
return Response(status=401, message='Genre must not be blank')
if (dataDict['description'] == None):
return Response(status=401, message='Description must not be blank')
if (dataDict['coverUrl'] == None):
return Response(status=401, message='Cover URL must not be blank')
conn = get_database_connection()
curs = conn.cursor()
try:
curs.execute(
"""
insert into books (title, author, genre, description, cover_url)
values (%s, %s, %s, %s, %s);
""", (title, author, genre, description, coverUrl))
dictionary['status'] = 200
dictionary['title'] = title
dictionary['author'] = author
dictionary['genre'] = genre
dictionary['description'] = description
dictionary['coverUrl'] = coverUrl
except psycopg2.Error as e:
print('err', e)
dictionary = {}
dictionary['status'] = 400
pass
conn.commit()
curs.close()
conn.close()
return build_response(dictionary, None)