Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

People also ask

How do you return a value from a stored procedure in MySQL?

To return a value from stored procedure, you need to use user defined session specific variable. Add @ symbol before variable name. Now second call for difference of two values. Call the stored procedure.

How do I call a SQL stored procedure in Python?

To call a stored procedure from a Python application, use ibm_db. callproc function. The procedure that you call can include input parameters (IN), output parameters (OUT), and input and output parameters (INOUT).

What is MySQLdb in Python?

MySQLdb is an interface for connecting to a MySQL database server from Python. It implements the Python Database API v2. 0 and is built on top of the MySQL C API. Packages to Install mysql-connector-python mysql-python.


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:

()