cursor callproc() appears to affect subsequent selects

[Paul]

Hello,

I'm attempting to transition a code base from using MySQLdb to
pymysql. I'm encountering the following problem and wonder if anyone
has seen something similar.

In a nutshell, if I call a stored procedure through the pymysql cursor
callproc() method a subsequent 'select' call through the execute()
method using the same or a different cursor returns incorrect results.
I see the same results for Python 2.7.2 and Python 3.2.2

Code is shown below:

conn = pymysql.connect(host='localhost', user='me',
passwd='pwd',db='mydb')

curr = conn.cursor()

rargs = curr.callproc("getInputVar", (args,))
resultSet = curr.fetchone()
print("Result set : {0}".format(resultSet))

# curr.close()
#
# curr = conn.cursor()

curr.execute('select * from my_table')
resultSet = curr.fetchall()
print("Result set len : {0}".format(len(resultSet)))

curr.close()
conn.close()

I can uncomment the close() and cursor creation calls above but this
doesn't change the result. I can also use a table name that does
not exist in the database in the select statement and this does not
generate an exception, so it appears that the execute request is not
making it to the server.

If I comment out the callproc() invocation the select statement works
just fine and an exception is generated if an incorrect table name is
used. If I completely close the connection and re-open it, that also
works fine

[Pete Hunt]

Can you break it down to a bare-minimum example that includes your stored proc definition?

Thanks.

Pete

[Paul]

The details are shown in the snippets below. The query just selects
all rows from any accessible table and prints the length. This works
before calling the stored procedure but not afterward, the returned
value from a fetchall() is None which generates the 'object of type
'NoneType' has no len()'
message.

Stored procedure (simply adds one on input parameter and returns it)

CREATE DEFINER=`root`@`localhost` PROCEDURE `getInputVar`( IN in_var
INT)
BEGIN
DECLARE var_output INT DEFAULT 0;

SELECT (in_var + 1) into var_output;
select var_output;
END

Python Code

conn = pymysql.connect(...)
curr = conn.cursor()

curr.execute('select * from test.books')
resultSet = curr.fetchall()
print("Number in result set : {0}".format(len(resultSet)))

args = 32

rargs = curr.callproc("getInputVar", (args,))

resultSet = curr.fetchall()

print("Result set : {0}".format(resultSet))

curr.execute('select * from test.books')
resultSet = curr.fetchall()
print("Number in result set : {0}".format(len(resultSet)))

Observed output:

Number in result set : 1
Result set : ((33L,),)
object of type 'NoneType' has no len()

[Paul]

Hello,

After some more testing and asking around it appears that placing a
call to the next_result() method of the connection object after
retrieving the results from the stored procedure using fetchall()
solves this problem.
I'm not at all sure this is the "best" solution but it is a solution
that may point toward it.

Thanks for the good work on pymysql it is much appreciated.

Paul

[Saran Lewprasert]

This save my day

with conn.cursor() as cur:

            cur.execute("CALL get_dashboard(%s)",username)

            resultSet = cur.fetchall()

            conn.next_result()

    return resultSet

[Saran Lewprasert]

if you want to get multiple resultset, you have to call fetchall then nextset then fetchall and finish with connection.next_result()

 with conn.cursor() as cur:

            cur.execute("CALL get_dashboard(%s)",username)

            resultSet = cur.fetchall()

            cur.nextset()

            resultSet2= cur.fetchall()

            conn.next_result()

    return [resultSet, resultSet2]