2

So I have an issue very similar to this question, but a bit different.

I am calling cursor.execute(sqlString) on a piece of sql that works fine when I run it directly on the mysql workbench. When I run the code however I get no result set.

I have exactly the same issue symptons as stated in the link and I have tried the linked solutions but it turns out that I do not have the same issue.

my _stored_results[] is empty when returning.

I am using the code in a try/except block, I have another python program that uses the same code to load a csv into a my mySQL db and it works dandy.

The code where I am having the issue is within an @app.route if that makes any differnce.

My code looks like this:

def functionName() :
    try:
        import mysql.connector
        from mysql.connector import errorcode

        cnx = mysql.connector.connect(user=init["dbDetails"][0], password=init["dbDetails"][1], host=init["dbDetails"][2], database=init["dbDetails"][3])
        cur = cnx.cursor()
        cur.close()         #I deffo don't need the two lines below but they were added for a sanity check, just to make sure the cur was not being read from any other code.
        cur = cnx.cursor()  # and this one obviously

        sqlString = 'CALL `schemaName`.`getProcedureName_sp`(1, 1, 0)'
        cur.execute(sqlString, multi=True) # tried it here without the multi=True and got the msg telling me to use it.

        getSomeDetails = cur.fetchall()

        cnx.commit() # probably don't need to commit here I am just reading from the dB but I am trying anything as I have no idea what my issue might be.

        return render_template('success.html')          

    except Exception as e:
        return render_template('error.html', error = str(e)) 
    finally:
        cur.close()
        cnx.close()

I am so baffled as I have this same code working in several places.

David McInerney
  • 21
  • 6

1 Answers1

0

So I was beating my head against the wall with this, and when I couldn't get anywhere, I just decided to leave it and move on, then come back with a fresh mind. Well... It worked, kinda.

So I haven't found the solution but I have found a work around that does the job and might even shed some light as to what is actually happening in my code.

I decided that as the fetchall() method was what was causing me the trouble I should try to circumvent it.

I probed the cursor(cur) just before the fetchall() method was called and saw that cur._rows contains the results from the SQL call.

So I changed the line 

getSomeDetails = cur.fetchall()

to

if len(cur._rows) > 0 :
    getSomeDetails = list(cur._rows[0]) #I only ever expect one result in this query

#getSomeDetails should now have the row I am looking for
getSomeDetails[0] #gets me the field I am looking for

and now my variable getSomeDetails has the return values from the procedure call

They are however not in the nice format that I should have gotten them from the fetchall() function, so I had to do some processing, I had to ensure that I was getting some values back and I noted that these values were returned in a tuple

I have come across this issue on two different machines running two different OS's and two different versions of python (Windows 7 with Python 2.7 and Windows 10 with Python 3) both pieces of code were different so obviously infact I was using two different MySQL libraries so the actual code for the fix was slightly different in both cases but I am now in both cases getting data from my DB into variables in Python, so that's cool.

However, this is a hack and I am aware of that, I would rather be using the proper function cur.fetchall() so I am still open to suggestions of what could be going wrong here.

David McInerney
  • 21
  • 6