Need help with this sql query for Postgres DB for a Python flask application

Question

@app.route("/home/search/details/<string:isbn_result_isbn>")
def book_search_results(isbn_result_isbn):
    book_details = db.execute("SELECT * FROM books WHERE isbn=:g", {"g": isbn_result_isbn}).fetchone()
    book_id = db.execute("SELECT * FROM book_reviews WHERE book_id=:v",{"v": book_details.id}).fetchall()
    return (f"{book_id}")

Unfortunately book_id is returned as blank.

However if the query is hard coded:

(book_id = db.execute("SELECT * FROM book_reviews WHERE book_id=89).fetchall())

I get a result. I know the problem is with book_details.id (that being an integer but is cast as a string in the sql query & hence I get no result), but can't seem to figure a way out of this.

Kindly help

Answer 1

I think there are several problems with your method. The first question would be: do you only want to return book_id for books that have a review? In your code:

book_details = db.execute("SELECT * FROM books WHERE isbn=:g", {"g": isbn_result_isbn}).fetchone()

If not mistaken, book_details is a tuple like:

(col1, col2, ...)

Second, why bother looking for the book_id in book_reviews if you already know what it is?

If you want to return the book_id for a particular ISBN, then:

book_id = db.execute("SELECT book_id FROM books WHERE isbn=:g", {"g": isbn_result_isbn}).fetchone()[0]

is sufficient. If you want to return the book_id only if it has been reviewed:

book_id = db.execute("SELECT book_id FROM books b WHERE isbn=:g and exists (select 1 from book_reviews r WHERE r.book_id=b.book_id)", {"g": isbn_result_isbn}).fetchone()[0]

One of the really nice things about python is multiline strings, so you could write it like:

sql = """SELECT book_id 
         FROM books b 
         WHERE isbn=:g
           AND EXISTS (
               SELECT 1 
               FROM book_reviews r 
               WHERE r.book_id=b.book_id
         )"""
book_id = db.execute(sql, {"g": isbn_result_isbn}).fetchone()[0]