0

I'm working on a book app. I've created a database that contains book data. I ask my users which book categories they like. Let say they tell me that they like 3 categories:

1: Literature & Fiction
2: Business & Money
3: Mystery, Thriller & Suspense

To fetch books from my local database I use this query: SELECT * FROM book WHERE category IN ("Literature & Fiction","Business & Money","Mystery, Thriller & Suspense") ORDER BY RAND() LIMIT 15"

Next I created an API using Flask. Here you see the route:

@app.route('/prefered_categories/<categories>')
def prefered_categories(categories):
    try:
        conn = mysql.connect()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        cursor.execute("SELECT * FROM book WHERE scrape_category IN %s ORDER BY RAND() LIMIT 15", categories)
        row = cursor.fetchall()
        resp = jsonify(row)
        resp.status_code = 200
        return resp
    except Exception as e:
        print(e)
    finally:
        cursor.close() 
        conn.close()

Running locally i tested this and got an 404: http://127.0.0.1:5000/prefered_categories/(%22Literature%20&%20Fiction%22,%22Business%20&%20Money%22,%22Mystery,%20Thriller%20&%20Suspense%22)

I'm not surprised by the 404. Because I don't know how to make this work properly. Does any of you have a solution to my problem?

UPDATE

You where right Martijn Pieters. I've updated my code with your suggestions. Currenty the code looks like this:

@app.route('/prefered_categories')
def prefered_categories():
categories = request.args['categories']

conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM book WHERE scrape_category IN (%s) ORDER BY RAND() LIMIT 15", categories)
row = cursor.fetchall()
resp = jsonify(row)
resp.status_code = 200
return resp

I've also changed IN %s to IN (%s) and & to %26
Now the API does work when I pas in a single category
For example http://127.0.0.1:5000/prefered_categories?categories=Romance returns 15 books of the Romance category.

When I try http://127.0.0.1:5000/prefered_categories?categories=%22Literature%20%26%20Fiction%22,%22Business%20%26%20Money%22,%22Mystery,%20Thriller%20&%20Suspense%22 it shows a 200 request that returns []

That means that the endpoint works but the parameter that was sent in the %s isn't recognized by MySQL so the query doesn't return any results. At least that is what I assume. Does any of you have suggestions on how to fix this?

T. de Jong
  • 91
  • 1
  • 1
  • 6
  • 1
    Did you restart your flask server after editing this, or made sure auto-reloading was enabled? Because your route implementation would never produce a 404 (you silence all exceptions, so at most you'd get a 500 error). The 404 would indicate your route is not being found *at all*. – Martijn Pieters Jan 11 '20 at 15:23
  • 1
    Also, putting such a complex string into the *path* of your route is not advisable. Use a query string instead, so `.../prefered_categories?categories=....`, with `@app.route('/prefered_categories')`, and `def prefered_categories():` and `categories = request.args['categories']`. – Martijn Pieters Jan 11 '20 at 15:27
  • 1
    And finally, don't use `try:..except Exception:`. Just let Flask handle the exception, it'll be logged to your console or if debug mode is on, visible in your browser, with full traceback. `print(e)` removes vital contextual information. – Martijn Pieters Jan 11 '20 at 15:28
  • @MartijnPieters Thanks for your suggestions. There was an old version of the API running in the background. That declares the 404's. Your suggestions where very helpful so far, do you know how to fix the next issue? – T. de Jong Jan 12 '20 at 18:29
  • SQL parameters encode **one** value. They ensure that a variable with multiple values or SQL syntax can't be used to break your query. You can't use a single parameter with `IN`, as you are just giving it one value, as far as the database is concerned. You'd have to generate the right number of placeholders. – Martijn Pieters Jan 13 '20 at 11:13
  • I found a suitable duplicate for you; you want to generate `%s` parameters with commas in between, then pass in the same number of parameter values. Do *not* include the quotes around those values. – Martijn Pieters Jan 13 '20 at 11:19
  • @MartijnPieters I wasn't able to fix the problem using that post. However I've simplified the question here: https://stackoverflow.com/questions/59722180/format-string-with-multiple-parameters-so-that-mysql-can-proces-them – T. de Jong Jan 13 '20 at 18:30

0 Answers0