Python, mysql.connector Error: No Result Set to Fetch From; cgitb shows correct value being passed to function

Question

Allright this one's got me baffled so I decided to see if I could find a response on here, I've searched up and down and several stackoverflow questions and answers and nothing has seemed to work. All I'm trying to do is a SELECT * FROM statement using mysql.connector and I keep getting a "No Result Set" error. Here's the code:

def session_fetch(value1):
    cnx = mysql.connector.connect(user='xxx', password='xxx', 
    host='127.0.0.1', database='xxx') 
    cursor = cnx.cursor()
    query = ("SELECT * FROM sessionkeys "
             "WHERE clientName='%s';") % value1 
    cursor.execute(query)
    row = cursor.fetchall()
    results = len(cursor.fetchall())
    clientName, clientAddr, unLocker = row[1], row[2], row[3]
    cnx.commit()
    cursor.close()
    cnx.close()

The error from cgitb shows: C:\inetpub\wwwroot\flighttoolsbz\validator.py in session_fetch(value1='ericdsmith86')
162 cursor.execute(query)
163 row = cursor.fetchall()
=> 164 results = len(cursor.fetchall())
165 clientName, clientAddr, unLocker = row[1], row[2], row[3]
166 cnx.commit()

InterfaceError: No result set to fetch from.
args = (-1, 'No result set to fetch from.', None)
errno = -1
msg = 'No result set to fetch from.'
sqlstate = None
with_traceback = built-in method with_traceback of InterfaceError object

But when I go through the MySQL workbench and run the same query using the same input value, it returns the one row i'm looking for, so it's definitely there. The only thing I can think of is that the %s formatter isn't taking what's being passed to the function as 'value1'. What am I missing?

Answer 1

You're calling cursor.fetchall() twice. You shouldn't be doing that.

Change:

row = cursor.fetchall()
results = len(cursor.fetchall())
clientName, clientAddr, unLocker = row[1], row[2], row[3]

To:

rows = cursor.fetchall()
results = len(rows) 
if results > 0:
    row = rows[0]
    clientName, clientAddr, unLocker = row[1], row[2], row[3]

And while it doesn't have anything to do with your current problem, you should be using a parameterized query:

query = "SELECT * FROM sessionkeys WHERE clientName=?" 
cursor.execute(query, (value1,))

Answer 2

As clockwatcher said, you called cursor.fetchall() twice, and his solution to fix it would solve the problem.

The way your SQL query itself is written leaves your code open to serious security vulnerabilities, since the query wouldn't escape the input parameters correctly. Similar to clockwatcher's response, a correct SQL query could be:

query = ("SELECT * FROM sessionkeys WHERE clientName='%s'", (value1,))

Also, since you aren't modifying any data, according to the mySQL connector documentation, there is no need to call the commit() method.

As such, incorporating all three changes, your code would look something like:

def session_fetch(value1):
    cnx = mysql.connector.connect(user='xxx', password='xxx', 
    host='127.0.0.1', database='xxx') 
    cursor = cnx.cursor()
    query = ("SELECT * FROM `sessionkeys` WHERE `clientName`='%s'", (value1,))
    cursor.execute(query)
    rows = cursor.fetchall()
    results = len(rows) 
    if results > 0:
        row = rows[0]
        clientName, clientAddr, unLocker = row[1], row[2], row[3]
    cursor.close()
    cnx.close()