Python Execute MySQL Stored Procedure

In this lesson, you will learn how to execute MySQL stored procedures in Python.

Prerequisites

Before moving further, make sure you have the following in place.

• Username and password that you need to connect to MySQL
• MySQL stored procedure name which you want to call.

For this lesson, I have created a stored procedure get_laptop under the “Electronics” database. This procedure fetches laptop details from the Laptop table based on the laptop id passed as an IN parameter

If a table is not present in your MySQL server, you can refer to our article to create a MySQL table from Python.

You can also download a SQL query file, which contains SQL queries for table creation and data so that you can use this table for your SQL operations.

If you already know the stored procedure you want to execute, you can skip the following query. Else, open the MySQL console and run the below query to create a MySQL Stored Procedure.

DELIMITER $
USE Electronics$
CREATE * from Laptop where id = d_id;
END $
DELIMITER ;

Now you know the stored procedure to execute, so let’s move to our example.

Note: We are using the MySQL Connector Python module to execute a MySQL Stored Procedure.

Steps to execute MySQL Stored Procedure in Python

To call MySQL stored procedure from Python, you need to follow these steps: –

Now, let see the example. In this example, we are going to execute the get_laptop stored procedure using Python.

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='Electronics',
                                         user='pynative',
                                         password='pynative@#29')
    cursor = connection.cursor()
    cursor.callproc('get_laptop', [1, ])
    # print results
    print("Printing laptop details")
    for result in cursor.stored_results():
        print(result.fetchall())

except mysql.connector.Error as error:
    print("Failed to execute stored procedure: {}".format(error))
finally:
    if (connection.is_connected()):
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

You should get the following output.

Printing laptop details
[(1, 'Lenovo ThinkPad P71', 6459.0, datetime.date(2019, 8, 14))]
MySQL connection is closed

Note: Also, catch any SQL exceptions that may occur during this process.
Use the MySQL Connector module’s Error class, which shows us an error when we failed to execute a stored procedure. Example ER_ACCESS_DENIED_ERROR when username or password is wrong.

Cursor callproc() Method

Syntax

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

The callproc() method calls the stored procedure mentioned in the proc_name argument. The args sequence of parameters must contain one entry for each argument that the procedure expects. For example, the procedure can have one or many IN and OUT parameters.

The callproc() returns a modified copy of the input sequence. This method doesn’t change the input parameters. However, It can replace the output and input/output parameters with new values as per the execution result.

The stored procedure returns the output in result sets, and It is automatically fetched and stored as MySQLCursorBuffered instances.

For example, a stored procedure “addition” takes two parameters, adds the values, and returns the sum.

CREATE PROCEDURE add_num(IN num1 INT, IN num2 INT, OUT sum INT)
BEGIN
SET sum := num1 + num2;
END;

The following example shows how to execute this Add procedure in Python.

args = (5, 6, 0) # 0 is to hold value of the OUT parameter sum
cursor.callproc('add_num', args)

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.