def book_details(book_id):
"""Display a single book's details and its review page"""
user_review = None
# Get Book Details:
book = db.execute("SELECT * FROM books WHERE id=:id", {
"id": book_id
}).fetchall()
# If book is not in database, return to homepage with apology:
if not book:
flash("Sorry, this book ID does not exist in the READ-RATE database!")
return redirect("/")
book = add_star_img(book)
# Get All Reviews and reviewer details for the Book:
reviews = db.execute(
"SELECT users.id, users.username, reviews.text, reviews.date, reviews.rating FROM users INNER JOIN reviews ON users.id=reviews.user_id WHERE reviews.book_id=:book_id ORDER BY reviews.date DESC",
{
"book_id": book_id
}).fetchall()
reviews = add_star_img(reviews)
reviews = form_time(reviews)
"""
# GoodReads API no longer available - Now switched to scraping the Goodreads website
# Get Additional Reviews and ratings from GoodReads API:
try:
gr_res = requests.get("https://www.goodreads.com/book/review_counts.json", params={"key": os.getenv("API_KEY"), "isbns": book[0][1]}).json()['books'][0]
except json.decoder.JSONDecodeError:
flash("Error with GoodReads API!")
return redirect("/")
good_reads = (gr_res['average_rating'], gr_res['work_ratings_count'])
"""
print(book, book[0][1], type(book[0][1]))
# Scrape GoodReads website for additional reviews/ratings:
good_reads = get_rating(book[0][1])
# If a user is logged in, check if they have left their own review:
if session.get("user_id"):
user_review = db.execute(
"SELECT id, user_id, text, date, rating FROM reviews WHERE user_id=:user_id AND book_id=:book_id",
{
"user_id": session["user_id"],
"book_id": book_id
}).fetchall()
user_review = add_star_img(user_review)
user_review = form_time(user_review)
return render_template("book_details.html",
book=book,
reviews=reviews,
good_reads=good_reads,
user_review=user_review)
def search():
""" Get results for a title, author or ISBN search """
# Get input from search bar
search_type = request.form.get("search-type")
search = request.form.get("search-text")
search_text = '%' + search + '%'
# If a search parameter is missing, render homepage with an apology
if not search_type or not search_text:
flash(
'Please select search type and enter a search value to search for books!'
)
return redirect("/")
# Otherwise check the search term and generate a query result:
author = None
title_isbn = None
if search_type == 'author':
author = []
# Get 10 authors:
author_names = db.execute(
"SELECT author FROM books WHERE author ILIKE :search_text GROUP BY author LIMIT 10",
{
"search_text": search_text
}).fetchall()
# For each author in list, get 6 books:
for name in author_names:
author_books = db.execute(
"SELECT * FROM books WHERE author = :author LIMIT 6", {
"author": name[0]
}).fetchall()
author_books = add_star_img(author_books)
author.append(author_books)
else:
# Get similar books by isbn or book title
title_isbn = db.execute(
f"SELECT * FROM books WHERE {search_type} ILIKE :search_text LIMIT 30",
{
"search_text": search_text
}).fetchall()
title_isbn = add_star_img(title_isbn)
return render_template("/search_results.html",
search_type=search_type,
search_text=search,
author=author,
title_isbn=title_isbn)
def user_details(user_id):
"""Display all the reviews written by a single user"""
# Get the username of the user:
username = db.execute("SELECT username FROM users WHERE id=:user_id", {
"user_id": user_id
}).fetchone()
# If username does not exist return to homepage with apology:
if not username:
flash("Sorry but this user does not exist!")
return redirect("/")
# Get all reviews by the user reviewer details for the Book:
reviews = db.execute(
"SELECT users.username, books.id, books.isbn, reviews.date, books.title, books.author, reviews.text, reviews.rating FROM users INNER JOIN reviews ON users.id=reviews.user_id INNER JOIN books ON reviews.book_id = books.id WHERE users.id=:user_id ORDER BY reviews.date DESC",
{
"user_id": user_id
}).fetchall()
reviews = add_star_img(reviews)
reviews = form_time(reviews)
return render_template("user_details.html",
username=username,
reviews=reviews)
def recommended():
""" Gets some simple book recommendations for a user based on their reviews """
# If user not logged in return to home page:
if session.get("user_id") == None:
flash("You must be logged in to get book recommendations!")
return redirect("/")
author_rec = None
books_rec = None
# Pick a book that the user has reviewed 4-5 stars, and if the author has some other books, recommend up to 6 of them to the user:
author_rec = db.execute(
"SELECT * FROM books WHERE author IN (SELECT books.author FROM books INNER JOIN reviews ON books.id=reviews.book_id WHERE reviews.user_id=:user_id AND reviews.rating >= 4 ORDER BY RANDOM() LIMIT 1) AND id NOT IN (SELECT book_id FROM reviews WHERE user_id=:user_id) ORDER BY RANDOM() LIMIT 6",
{
"user_id": session["user_id"]
}).fetchall()
author_rec = add_star_img(author_rec)
# Pick a book that the user has reviewed highly, find users that also reviewed this book highly, and find other books they enjoyed:
hr_book = db.execute(
"SELECT books.id, books.title, books.author FROM books INNER JOIN reviews ON books.id=reviews.book_id WHERE reviews.user_id=:user_id AND reviews.rating >= 4 ORDER BY RANDOM() LIMIT 1",
{
"user_id": session["user_id"]
}).fetchone()
if hr_book:
books_rec = db.execute(
"SELECT * FROM books WHERE id IN (SELECT book_id FROM reviews WHERE user_id IN (SELECT user_id FROM reviews WHERE book_id=:book_id AND rating >=4 AND user_id!=:user_id) AND book_id!=:book_id GROUP BY book_id ORDER BY AVG(rating) DESC LIMIT 6)",
{
"book_id": hr_book[0],
"user_id": session["user_id"]
}).fetchall()
books_rec = add_star_img(books_rec)
return render_template("recommended.html",
author_rec=author_rec,
hr_book=hr_book,
books_rec=books_rec)
def index():
""" Home Page of the Application """
# Top Rated Books - select 6 highest rated books:
top = db.execute(
"SELECT * FROM books WHERE average_rating >= 4.5 ORDER BY RANDOM() LIMIT 6"
).fetchall()
top = add_star_img(top)
# Lucky Dip Section - select 6 random books:
lucky = db.execute(
"SELECT * FROM books ORDER BY RANDOM() LIMIT 6").fetchall()
lucky = add_star_img(lucky)
# Author Explore Section - select up to 6 books from an author:
author = db.execute(
"SELECT * FROM books WHERE author in (SELECT author FROM books GROUP BY author ORDER BY RANDOM() LIMIT 1) LIMIT 6"
).fetchall()
author = add_star_img(author)
return render_template("home.html", top=top, lucky=lucky, author=author)
def author_details(name):
"""Display all books by a given author"""
# Author Explore Section - select up to 4 books from an author:
author = db.execute("SELECT * FROM books WHERE author=:author", {
"author": name
}).fetchall()
# If author does not exist then return home with apology:
if not author:
flash(
"Sorry but that author could not be found in the READ-RATE database!"
)
return redirect("/")
author = add_star_img(author)
return render_template("author_details.html", author=author, lucky=author)