Getting return values from a MySQL stored procedure in Python, using MySQLdb

Question

I've got a stored procedure in a MySQL database that simply updates a date column and returns the previous date. If I call this stored procedure from the MySQL client, it works fine, but when I try to call the stored procedure from Python using MySQLdb I can't seem to get it to give me the return value.

Here's the code to the stored procedure:

CREATE PROCEDURE test_stuff.get_lastpoll()
BEGIN
 DECLARE POLLTIME TIMESTAMP DEFAULT NULL;
 START TRANSACTION;
 SELECT poll_date_time
 FROM test_stuff.poll_table
 LIMIT 1 
 INTO POLLTIME
 FOR UPDATE;
 IF POLLTIME IS NULL THEN
 INSERT INTO 
 test_stuff.poll_table 
 (poll_date_time)
 VALUES 
 ( UTC_TIMESTAMP() );
 COMMIT;
 SELECT NULL as POLL_DATE_TIME;
 ELSE
 UPDATE test_stuff.poll_table SET poll_date_time = UTC_TIMESTAMP();
 COMMIT;
 SELECT DATE_FORMAT(POLLTIME, '%Y-%m-%d %H:%i:%s') as POLL_DATE_TIME;
 END IF;
END

The code I'm using to try to call the stored procedure is similar to this:

#!/usr/bin/python
import sys
import MySQLdb
try:
 mysql = MySQLdb.connect(user=User,passwd=Passwd,db="test_stuff")
 mysql_cursor = mysql.cursor()
 results=mysql_cursor.callproc( "get_lastpoll", () )
 print results
 mysql_cursor.close()
 mysql.close()
except MySQLdb.Error, e:
 print "MySQL Error %d: %s" % ( e.args[0], e.args[1] )
 sys.exit(1)

I know that you can do IN and OUT parameters, but from what I can determine from the MySQLdb documentation, this isn't possible with MySQLdb. Does anyone have any clue how I could get the results of the stored procedure?

If I run it from a SQL tool, here's the output:

POLL_DATE_TIME 
------------------- 
2009年02月18日 22:27:07 

If I run the Python script, it returns back an empty set, like this:

()

Answer 1

What I had to do is modify the Python code to use execute() instead of callproc(), and then use the fetchone() to get the results. I'm answering it myself since mluebke's answer wasn't entirely complete (even though it was helpful!).

mysql_cursor.execute( "call get_lastpoll();" )
results=mysql_cursor.fetchone()
print results[0]

This gives me the correct output:

2009年02月19日 17:10:42

See https://stackoverflow.com/a/52715128/2391795 for advanced usage of fetchone, fetchall, etc.

• sandeep: How to pass argument to the cursor in this case

Answer 2

callproc also works fine, you don't need to use execute:

mysql_cursor.callproc( "get_lastpoll", () )
result = mysql_cursor.fetchone()

Answer 3

You still have to fetch the results.

results = cursor.fetchone()

or

results = cursor.fetchall()

• Vadorequest: What does those functions relate to? A link to the official doc would have been better

Answer 4

From the API documentation for the MySQLdb library. You will need to call cursor_obj.nextset() before you will see the result set returned by the stored procedure. This is because the call to the stored procedure creates a result set. The result sets returned by the stored procedure follow.

More info

• Air: As the documentation you linked states, the extra empty result set is returned after the result sets generated by the stored procedure. Calling nextset() before fetching discards the wrong result set.