MySQL :: MySQL Connector/Python Developer Guide :: 10.5.5 MySQLCursor.callproc() Method (original) (raw)

10.5.5 MySQLCursor.callproc() Method

Syntax:

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

This method calls the stored procedure named by theproc_name argument. Theargs 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 asMySQLCursorBuffered instances. For more information about using these result sets, seestored_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 themultiply() 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 proceduresp1() 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 theOUT parameter, do this:

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