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?

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). — Blckknght
– Blckknght, Commented Jan 5, 2017 at 0:55
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. — pvg
– pvg, Commented Jan 5, 2017 at 0:56
@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. — Jimmy
– Jimmy, Commented Jan 5, 2017 at 1:00
fetchall returns a list and I don't think you can pass that result to dict(). What is the ideal result you expected? — Philip Tzou
– Philip Tzou, Commented Jan 5, 2017 at 1:19
@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 — pvg
– pvg, Commented Jan 5, 2017 at 1:49

Philip Tzou · Accepted Answer · 2017-01-05 01:48:18Z

1

The problems:

As @pvg mentioned, you need to escape your input values when querying database;
If you want to fetch a dictionary-like result, passing dictionary=True when you initialize the cursor;
In your original code, you didn't return the variable json_output;
To fetch only one result, use fetchone instead fetchall;
After cursor.close() got called, you can obtain nothing from that cursor no matter you fetched before or not;
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()

edited Jan 5, 2017 at 1:48

answered Jan 5, 2017 at 1:23

Philip Tzou

6,5582 gold badges22 silver badges31 bronze badges

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

2 Comments

Jimmy Over a year ago

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 Over a year ago

@Jimmy: glad you solved that. I added the explanation to my answer.

Collectives™ on Stack Overflow

python cursor.execute returning empty

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related