Getting return values ​​from MySQL stored procedure in Python using MySQLdb

Question

I have a stored procedure in a MySQL database that just updates the date column and returns the previous date. If I call this stored procedure from the MySQL client, it will work fine, but when I try to call the stored procedure from Python using MySQLdb, I can not get it to return the return value to me.

Here's the stored procedure code:

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 use to try to call the stored procedure is similar to the following:

 #!/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 the IN and OUT parameters, but from what I can determine from the MySQLdb documentation, this is not possible in MySQLdb. Does anyone know how I can get the results of a stored procedure?

If I ran it from an SQL tool, here is the output:

 POLL_DATE_TIME ------------------- 2009-02-18 22:27:07 

If I run a Python script, it will return an empty set back, for example:

 () 

Answer 1

I needed to modify Python code to use execute () instead of callproc (), and then use fetchone () to get the results. I answer myself because mluebke's answer was not completely complete (although it was useful!).

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

This gives me the correct result:

 2009-02-19 17:10:42 

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 should still get results.

 results = cursor.fetchone() 

or

 results = cursor.fetchall() 

etc..

Answer 4

MySQLdb API documentation for the MySQLdb library. You will need to call cursor_obj.nextset() before you see the result set returned by the stored procedure. This is because calling a stored procedure creates a result set. After that, the result sets returned by the stored procedure are saved.

Additional Information