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)