OverIQ.com

  1. Home
  2. MySQL Connector/Python Tutorial
  3. Calling Stored Procedures using Connector/Python

Calling Stored Procedures using Connector/Python

Last updated on July 27, 2020

What is Stored Procedure? #

A Stored procedure is simply a set of SQL statements grouped together and given a name. Stored procedures are used in situations where you want to execute the same set of statements multiple number of times. We create stored procedures using the CREATE PROCEDURE statement. Stored procedures can also accept arguments. Once the stored procedure is created, we can invoke it using the CALL statement followed by the procedure name, followed by arguments (if any) inside the parentheses.

Here is a simple stored procedure that accepts a continent and returns the name of the countries in it.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
mysql> use world;
mysql> 
mysql> DELIMITER //
mysql> CREATE PROCEDURE get_country_by_continent
    -> (
    ->      IN con CHAR(20)
    -> )
    -> BEGIN
    ->   SELECT Name   
    ->   FROM country
    ->   WHERE Continent = con;
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql>

Note that in the preceding snippet the con argument is preceded with the IN keyword, thus it is an input parameter. An input parameter is used to pass values to the stored procedure from the calling program. A stored procedure can change the value of the input parameter but this change will be not visible to calling the program when the procedure returns. If you want to return values from the stored procedures then declare the parameter as output parameter using the OUT keyword instead of IN. The initial value of the output parameter is NULL.

In addition to input and output parameters, there exists another kind parameter called input/output parameter. As the name suggests, an input/output parameter can be used as an input parameter as well as an output parameter. In other words, input/output parameter can be used to pass values to the stored procedures. Further, a stored procedure can modify the input/output parameter and return it to the calling program. To create input/output parameter precede the variable name with the INOUT keyword.

Let's create a stored procedure that uses both input and output parameters.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> 
mysql> DELIMITER //
mysql> CREATE PROCEDURE get_country_info
    -> (
    ->   IN country CHAR(20),
    ->   OUT info VARCHAR(200)
    -> )
    -> BEGIN
    ->   SELECT CONCAT_WS(":", Name, Continent, Population, LifeExpectancy, HeadOfState)  
    ->   INTO info
    ->   FROM country
    ->   WHERE Name = country;
    -> 
    ->   select city.Name, city.Population, city.District from city
    ->   INNER JOIN country
    ->   where city.CountryCode = country.Code and country.Name= country
    ->   order by Population desc limit 10;
    -> 
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql>

Here is how this stored procedure works:

  1. The stored procedure accepts two arguments: country and info. The country is the input parameter and info is the output parameter.

  2. Next, we use the SELECT statement to fetch some relevant data about the country. To store the data into the output parameter we use the INTO clause.

  3. Finally, we print the top 10 populated cities of the country in descending order.

We now have two stored procedures in the world database. To view them type the following command:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql> show PROCEDURE STATUS where db = "world";
+-------+--------------------------+-----------+----------------+---------------------+---------------------+
| Db    | Name                     | Type      | Definer        | Modified            | Created             |
+-------+--------------------------+-----------+----------------+---------------------+---------------------+
| world | get_country_by_continent | PROCEDURE | root@localhost | 2018-10-05 10:28:37 | 2018-10-05 10:28:37 |
| world | get_country_info         | PROCEDURE | root@localhost | 2018-10-05 11:25:35 | 2018-10-05 11:25:35 |
+-------+--------------------------+-----------+----------------+---------------------+---------------------+
2 rows in set (0.01 sec)

mysql>

Note: Some columns are in preceding output is omitted to save the space.

To call the get_country_by_continent stored procedure use the CALL statement as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> 
mysql> CALL get_country_by_continent("South America");
+------------------+
| Name             |
+------------------+
| Argentina        |
| Bolivia          |
| Brazil           |
| Chile            |
| Colombia         |
| Ecuador          |
| Falkland Islands |
| French Guiana    |
| Guyana           |
| Peru             |
| Paraguay         |
| Suriname         |
| Uruguay          |
| Venezuela        |
+------------------+
14 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Similarly, we can call get_country_info as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> 
mysql> call get_country_info("Brazil", @info);
+----------------+------------+-------------------+
| Name           | Population | District          |
+----------------+------------+-------------------+
| São Paulo      |    9968485 | São Paulo         |
| Rio de Janeiro |    5598953 | Rio de Janeiro    |
| Salvador       |    2302832 | Bahia             |
| Belo Horizonte |    2139125 | Minas Gerais      |
| Fortaleza      |    2097757 | Ceará             |
| Brasília       |    1969868 | Distrito Federal  |
| Curitiba       |    1584232 | Paraná            |
| Recife         |    1378087 | Pernambuco        |
| Porto Alegre   |    1314032 | Rio Grande do Sul |
| Manaus         |    1255049 | Amazonas          |
+----------------+------------+-------------------+
10 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Notice how the output parameter (i.e @info) is passed to the procedure. After the procedure executes, we can print the value of the output parameter using the SELECT statement as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql> 
mysql> select @info;
+---------------------------------------------------------------+
| @info                                                         |
+---------------------------------------------------------------+
| Brazil:South America:170115000:62.9:Fernando Henrique Cardoso |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Calling Stored Procedures with callproc() #

To call stored procedures we use the callproc() method of the cursor object. The syntax of the callproc() method is as follows:

callproc(proc_name, args=())
Argument Description
proc_name The stored procedure you want to call
args A sequence of arguments to pass to the stored procedure

On success, the callproc() returns a modified version of the input sequence. The input parameters (i.e. parameter coded with the IN keyword ) are left untouched but the input and output parameters (i.e. parameters coded with the OUT and INOUT keyword, respectively) may contain new values.

Once the stored procedure is executed, you can fetch the results using the stored_results() method of the cursor object. The stored_results() returns an iterator which can be used to iterate over the rows in the result set.

It is important to note that the result set produced by the stored procedure is fetched immediately and stored as MySQLCursorBuffered instances.

The following listing demonstrates how to use the callproc() in action:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
import mysql.connector
from pprint import pprint

db = mysql.connector.connect(option_files='my.conf')

cursor = db.cursor()

args = cursor.callproc('get_country_by_continent', ('South America',) )
print("Return value:", args)

for result in cursor.stored_results():
    pprint(result.fetchall())

print('-' * 100)

args = cursor.callproc('get_country_info', ('Japan', None) )
print("Return value:", args)

for result in cursor.stored_results():
    pprint(result.fetchall())

Expected Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Return value: ('South America',)
[('Argentina',),
 ('Bolivia',),
 ('Brazil',),
 ('Chile',),
 ('Colombia',),
 ('Ecuador',),
 ('Falkland Islands',),
 ('French Guiana',),
 ('Guyana',),
 ('Peru',),
 ('Paraguay',),
 ('Suriname',),
 ('Uruguay',),
 ('Venezuela',)]
----------------------------------------------------------------------------------------------------
Return value: ('Japan', 'Japan:Asia:126714000:80.7:Akihito')
[('Tokyo', 7980230, 'Tokyo-to'),
 ('Jokohama [Yokohama]', 3339594, 'Kanagawa'),
 ('Osaka', 2595674, 'Osaka'),
 ('Nagoya', 2154376, 'Aichi'),
 ('Sapporo', 1790886, 'Hokkaido'),
 ('Kioto', 1461974, 'Kyoto'),
 ('Kobe', 1425139, 'Hyogo'),
 ('Fukuoka', 1308379, 'Fukuoka'),
 ('Kawasaki', 1217359, 'Kanagawa'),
 ('Hiroshima', 1119117, 'Hiroshima')]

Notice that in the second call to callproc() method, we are passing None as the second argument this is because the get_country_info stored produced accepts two arguments (one input and one output).

MySQL Connector/Python Tutorial

Recent Posts