Pass three parameters to MySQL using Flask REST API

Question

I need to build a route in flask using python that would allow 3 parameters to be passed via URI so I can use the 3 parameters in a MySQL query.

The database table has each of the 3 parameters in 3 columns and the end result query would be something like:

Select * from maintable where (field1= param1 and field2 = param2 and field3 = param3);

I’d like the URI to look like: http://my.api.com/search/123/345/DD5432

The python code looks like this

@app.route('/search/param1/param2/param3')
def get():
cur = mysql.connect().cursor()
cur.execute('''select * from maindb.maintable''')
r = [dict((cur.description[i][0], value)
          for i, value in enumerate(row)) for row in cur.fetchall()]
return jsonify({'results' : r})

I have so far been able to successfully pass 1 parameter and use that to query 1 column in my database.

I'm not clear where you are stuck. Your current code doesn't pass any parameters. Are you having trouble getting parameters from the URL, or passing them into the SQL? — Daniel Roseman
– Daniel Roseman, Commented Sep 16, 2018 at 7:19

Ivan Velichko · Accepted Answer · 2018-09-16 07:26:18Z

2

First of all you need to change the route rule to be able to extract parts of it. And then you need to send these parameters within the SQL query. However, don't build your SQL query from the user input directly since it could introduce an SQL injection vulnerability. Use placeholders and then submit your params as a tuple to the DB cursor execute():

@app.route('/search/<param1>/<param2>/<param3>')
def get(param1, param2, param3):
    cur = mysql.connect().cursor()
    cur.execute('''select * from maindb.maintable where field1 = %s and field2 = %s and field3 = %s''', 
                (param1, param2, param3))
    r = [dict((cur.description[i][0], value)
         for i, value in enumerate(row)) for row in cur.fetchall()]
    return jsonify({'results': r})

edited Sep 16, 2018 at 7:26

answered Sep 16, 2018 at 7:20

Ivan Velichko

6,7597 gold badges49 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Neil Over a year ago

Great - that works! The only thing is the results[] are empty. I'm expecting it to return all fields (select * from ....). I'm getting a 200 OK when i use values that are in the db.

Neil Over a year ago

Thanks. That helped. The issue i have is that sometimes param1 or param2 are null. I think i need to add a case statement to handle this?

Neil Over a year ago

I've tried using CASE in the SQL query to handle null or empty parameters. However an error seems to occur earlier with Flask. The API requirements include situations where param2 is empty or param1 and param2 are empty. For example the URI could be: my.api.com/search/123//DD5432 or my.api.com/search///DD5432. A workaround is to change my dataset to use '0' instead of '' so a URI of my.api.com/search/123/0/DD5432 or my.api.com/search/0/0/DD5432 - which Flask accepts and SQL returns correctly.

Collectives™ on Stack Overflow

Pass three parameters to MySQL using Flask REST API

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related