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.
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).
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:
()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With