10.5.5 MySQLCursor.callproc() Method

Syntax:

result_args = cursor.callproc(proc_name, args=())

This method calls the stored procedure named by the proc_name argument. The args sequence of parameters must contain one entry for each argument that the procedure expects. callproc() returns a modified copy of the input sequence. Input parameters are left untouched. Output and input/output parameters may be replaced with new values.

Result sets produced by the stored procedure are automatically fetched and stored as MySQLCursorBuffered instances. For more information about using these result sets, see stored_results().

Suppose that a stored procedure takes two parameters, multiplies the values, and returns the product:

CREATE PROCEDURE multiply(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)
BEGIN
  SET pProd := pFac1 * pFac2;
END;

The following example shows how to execute the multiply() procedure:

>>> args = (5, 6, 0) # 0 is to hold value of the OUT parameter pProd
>>> cursor.callproc('multiply', args)
('5', '6', 30L)

Connector/Python 1.2.1 and up permits parameter types to be specified. To do this, specify a parameter as a two-item tuple consisting of the parameter value and type. Suppose that a procedure sp1() has this definition:

CREATE PROCEDURE sp1(IN pStr1 VARCHAR(20), IN pStr2 VARCHAR(20),
                     OUT pConCat VARCHAR(100))
BEGIN
  SET pConCat := CONCAT(pStr1, pStr2);
END;

To execute this procedure from Connector/Python, specifying a type for the OUT parameter, do this:

args = ('ham', 'eggs', (0, 'CHAR'))
result_args = cursor.callproc('sp1', args)
print(result_args[2])