python cursor.execute returning empty

Question

I have a problem with my python code which I want to use for a REST API server.

The current problem is that my database query is returning null when I know that the value is there

The code for the specific path:

@app.route('/data/active_predicted/<int:ticketId>', methods=['GET'])
def search_db_tickId_act(ticketId):
 cursor = db.cursor()
 db_query = cursor.execute("select * from active_predicted where ticketId=" + str(ticketId))
 json_output = json.dumps(dict(cursor.fetchall()))
 cursor.close()
 if not cursor.fetchall():
 return "Nothing found \n SQL Query: " + "select * from active_predicted where ticketId=" + str(ticketId)
 else:
 return str(cursor.fetchall())

When I access this URL I get returned the following:

Nothing found SQL Query: select * from active_predicted where ticketId=1324

When I plug this SQL query I get the result I want, 1 row with 2 columns but it seems as though the program cannot locate the row?

• Blckknght: I'd guess you're consuming all the results in the line json_output = json.dumps(dict(cursor.fetchall())), but I'm not a Python DB expert. I'd also suggest you probably don't want to be using string manipulation for constructing your query (the DB interface has its own safer way of specifying arguments).
• pvg: First, don't construct queries by string concatenation, your program is vulnerable to sql injection as is. cursor is also named for a reason - it progresses forward through the result set so you can't keep calling 'fetchall' repeatedly - the first fetchall fetches all results.
• Jimmy: @pvg thank you for the tips, I am used to java programming so I wasn't too sure concerning the functions. Also, thank you for the tip regarding SQL Injection. The problem is that even without the if else statement at the bottom, the JSON object that was returned still did not have any data inside of it.
• Philip Tzou: fetchall returns a list and I don't think you can pass that result to dict(). What is the ideal result you expected?
• pvg: @Jimmy np, you might want to read up on the python db api (whatever driver you are using implements it). the basic concepts of cursor and prepared statements are very similar to JDBC

Answer 1

The problems:

1. As @pvg mentioned, you need to escape your input values when querying database;
2. If you want to fetch a dictionary-like result, passing dictionary=True when you initialize the cursor;
3. In your original code, you didn't return the variable json_output;
4. To fetch only one result, use fetchone instead fetchall;
5. After cursor.close() got called, you can obtain nothing from that cursor no matter you fetched before or not;
6. Use try-finally to ensure that cursor always get closed (at last).

Here's the fixed code:

@app.route('/data/active_predicted/<int:ticketId>', methods=['GET'])
def search_db_tickId_act(ticketId):
 try:
 cursor = db.cursor(dictionary=True)
 db_query = cursor.execute("select * from active_predicted where ticketId=%s LIMIT 1", ticketId)
 row = cursor.fetchone()
 if row:
 return json.dumps(row)
 else:
 return "Nothing found \n SQL Query: " + "select * from active_predicted where ticketId=" + str(ticketId)
 finally:
 cursor.close()

• Jimmy: Thanks! This solution works for me. I had to change some values due to errors but the code mostly stayed the same. Can you tell me what is different from this code and my original as well as why my original code would not work? Thank you very much once again!
• Philip Tzou: @Jimmy: glad you solved that. I added the explanation to my answer.