0
@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

4
  • Hi and welcome to the forum. I'm not a Python man and I think this question is more suitable for SO, but have you tried to change isbn=:g, book_id=:v by isbn=g and book_id=g?
    – McNets
    Jun 27 '20 at 8:27
  • There is no string cast in your queries. Show the table definitions.
    – CL.
    Jun 27 '20 at 8:54
  • 1
    If you have a problem with the actual SQL query, then please only show the SQL code. If you have a problem with the combination of Python and SQL, then this should be asked on stackoverflow.com Jun 27 '20 at 9:39
  • Are you sure book_details.id is what you think it is? I would have guessed that book_details is a tuple. You may want to try with book_details[0] as the argument for the second query. As a side-note, I would also suggest that you spell out the columns instead of using * in your queries.
    – Lennart
    Jun 27 '20 at 13:53
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]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.