def api(isbn_id):
book_api = db.execute("SELECT * FROM books WHERE isbn = :isbn", {
"isbn": isbn_id
}).fetchone()
if book_api is None:
goodreads = get_goodreads(isbn_id)
if goodreads.status_code != 200:
return render_template("error.html", message="404 Error")
else:
book_api_other = goodreads.json()
return book_api_other
else:
book_reviews = db.execute(
"SELECT COUNT(id), AVG(stars) FROM reviews WHERE bookid = :bookid",
{
"bookid": book_api.id
}).fetchone()
book_reviews = db.execute(
"SELECT COUNT(id), AVG(stars) FROM reviews WHERE bookid = :bookid", {
"bookid": book_api.id
}).fetchone()
return jsonify({
"title": book_api.title,
"author": book_api.author,
"year": book_api.year,
"isbn": book_api.isbn,
"review_count": str(book_reviews[0]),
"average_score": str(book_reviews[1])
}), 200
def book(book_id):
book = db.execute("SELECT * FROM books WHERE id = :id", {
"id": book_id
}).fetchone()
goodreads = get_goodreads(book.isbn)
if goodreads.status_code != 200:
return render_template("error.html", message="404 Error")
book_all = goodreads.json()
book_rating = book_all["books"][0]["average_rating"]
# pdb.set_trace()
reviews = db.execute(
"SELECT * FROM reviews LEFT JOIN public.users ON (reviews.user_id = users.id) WHERE book_id = :id",
{
"id": book_id
},
).fetchall()
return render_template("book.html",
book=book,
book_rating=book_rating,
reviews=reviews)
def api(isbn_id):
book_api = db.execute("SELECT * FROM books WHERE isbn = :isbn", {
"isbn": isbn_id
}).fetchone()
if book_api is None:
goodreads = get_goodreads(isbn_id)
if goodreads.status_code != 200:
return abort(404)
else:
book_api = goodreads.json()
return book_api
else:
book_reviews = db.execute(
"SELECT COUNT(id), AVG(stars) FROM reviews WHERE book_id = :book_id",
{
"book_id": book_api.id
},
).fetchone()
resp = {}
resp["title"] = book_api.title
resp["author"] = book_api.author
resp["year"] = book_api.year
resp["isbn"] = book_api.isbn
try:
resp["review_count"] = str(book_reviews[0])
resp["average_score"] = "% 1.1f" % book_reviews[1]
except TypeError:
resp["review_count"] = "Not enough reviews"
resp["average_score"] = "Not enough reviews"
json_resp = json.dumps(resp)
return json_resp, 200
def book(isbn):
if request.method == 'GET':
book = db.execute(text("SELECT title, author, year FROM books WHERE isbn = :id").bindparams(id = isbn)).fetchone()
if len(book) == 0:
return render_template("error.html", message = 'No books found! Please try again')
# Get reviews for books
query = text("SELECT score, description, u.name FROM reviews r INNER JOIN users u ON r.user_id = u.id WHERE r.isbn = :id").bindparams(id = isbn)
reviews = db.execute(query).fetchall()
avg_score_res = db.execute(text("SELECT coalesce(avg(score), 0) score, count(*) reviews FROM reviews WHERE isbn = :id").bindparams(id = isbn)).fetchone()
avg_score = {"score": round(avg_score_res['score'], 2), "reviews": avg_score_res['reviews'], "reviews_f" : "{:,}".format(avg_score_res['reviews'])}
# Has the user already submitted a review?
query_rev = text("SELECT count(*) rev FROM reviews WHERE isbn = :isbn AND user_id = :uid").bindparams(isbn = isbn, uid = session['user_id'])
check_already = db.execute(query_rev).fetchone()
# Get goodreads info
goodr = get_goodreads(isbn = isbn)
return render_template("book.html", book = book, bw_score = avg_score, gr_info = goodr, reviews = reviews, check_already = check_already)
if request.method == 'POST':
if not request.form.get('content') or not request.form.get('rating'):
return render_template("error.html", message = "Please enter a valid review!")
desc = request.form.get('content')
rating = request.form.get('rating')
userid = session['user_id']
query = text("INSERT INTO reviews (user_id, isbn, score, description) VALUES (:uid, :isbn, :score, :desc)")
query = query.bindparams(uid = userid, isbn = isbn, score = rating, desc = desc)
db.execute(query)
db.commit()
return render_template("message.html", message = 'Thanks for submitting your review!')