Calling MySQL Stored Procedures in Python

In the previous tutorial, we learned how to connect the MySQL database to Python. In that tutorial, we will go through DB connection, DDL and DML queries, etc. In this tutorial, we will learn how to create stored procedures and call them in Python program.

This is going to be a detailed yet straightforward guide, and we will learn everything step by step, starting from creating a table and procedures in MySQL. Then we will call these procedures in the Python program. Here we go!

In this tutorial, we will not explain what stored procedure is because it is considered you already have knowledge about stored procedures in MySQL.

Prerequisites

Before writing a program in Python, make sure you have started the MySQL server, and it is working fine. Then we have to create stored procedures in MySQL through the command line. However, you may prefer to write MySQL statements using MySQL workbench.

Make sure you are good with the Python-MySQL connection. We have created the complete guide; if you don’t know how to connect MySQL database with Python, check it out from here.

That’s all we need. Let’s dive into the tutorial.

Before We Start

Before starting the actual tutorial, let’s understand what things we are going to achieve.

We will create a table to hold the details of the laptops. Then we will create two stored procedures; one with no parameter and another one with one parameter. After that, we will write a Python program and call those stored procedures one by one.

Syntax to Call MySQL Stored Procedure in Python

Calling a stored procedure in Python is as easy as writing a simple query. The syntax is straightforward and requires no additional statements or many lines of code.

Here is the syntax to call stored procedure in Python

result_args = cursor.callproc(proc_name, args=())Code language: SQL (Structured Query Language) (sql)

Example-

cursor.callproc(laptop_by_name,["lenovo"];Code language: SQL (Structured Query Language) (sql)

Where,

• cursor is a Python object to execute statements and communicate with MySQL DB.
• callproc is a functon to call stored procedures.
• proc_name is a stored procedure name.
• args are the stored procedrure arguments.

A modified version of the input sequence is returned by callproc(). This approach does not change the supplied parameters. However, it can replace the output and input/output parameters with new values depending on the execution result.

The result set of the above statement is automatically fetched and stored as a MySQLCursorBuffered instance, and you can use the stored_result() method to get the result set values.

Example of calling procedure in Python

Let’s demonstrate this by creating a table, creating a stored procedure and then calling it in our Python program.

Create table and Insert Data into It

Let’s start by creating a table named laptops and then insert data into it.

CREATE TABLE laptops(
id INT PRIMARY KEY AUTO_INCREMENT,
model_no INT,
model_name VARCHAR(100),
price FLOAT,
insurance FLOAT,
quantity INT
);Code language: SQL (Structured Query Language) (sql)

INSERT INTO laptops(model_no,model_name,price,insurance,quantity)
VALUES(12314,"Lenovo Ideapad",41490,180,5),
(57823,"Dell Vostro 3000",40990,130,9),
(17827,"Asus Vivobook",35000,130,8),
(12879,"HP Ryzen 3",39990,120,9),
(23098,"Asus Pentium Q",28990,180,4),
(18778,"Dell Inspiron 3",45990,130,5);Code language: SQL (Structured Query Language) (sql)

Let’s check if the data is inserted successfully in the table.

SELECT * FROM laptops;Code language: SQL (Structured Query Language) (sql)

Create Stored Procedures

Perfect! Data inserted successfully. Using the MySQL CLI, let’s create a procedure to fetch all the laptop details with a combined price (price+insurance).

DELIMITER //
mysql> CREATE PROCEDURE lap_details()
BEGIN
SELECT id, model_no,model_name,price as base_price,
(price+insurance) AS total_price, quantity
FROM laptops;
END 
//Code language: SQL (Structured Query Language) (sql)

We have created lap_details() stored procedure which consists of a SELECT statement with a simple addition of two columns- price and insurance and the new column will be created with the name “total_price”.

Note that no parameter is defined for the procedure. If you try to pass the parameter while calling it, you will end up with an error.

Let’s call the lap_details stored procedure to see the result.

CALL lap_details();Code language: SQL (Structured Query Language) (sql)

Now, we will create another procedure with one IN parameter and a SELECT statement.

CREATE PROCEDURE lap_by_names(IN name VARCHAR(100))
BEGIN
SELECT id, model_no,model_name,price as base_price,
insurance,(price+insurance) AS total_price, quantity
FROM laptops
WHERE model_name LIKE CONCAT('%',name,'%');
END
//Code language: SQL (Structured Query Language) (sql)

This procedure will search for a particular laptop by the entered name and return all the matched results.

We have passed a parameter “name” which will consist of a laptop name. The procedure will search for the given keyword in the table using the WHERE clause and LIKE operator and return the result.

Let’s call the lap_by_names procedure and check the result.

CALL lap_by_names("lenovo");Code language: SQL (Structured Query Language) (sql)

We have got desired output by the procedure. Let’s call these stored procedures using a Python program.

Call Stored Procedures From a Python Program

Follow the steps given below in order to call the stored procedures in the Python program.

• Create a database connection using a mysql connector and proper credentials.
• Create a cursor object.
• Call the call_proc() method to call the procedure.
• Iterate through the obj.stored_results() and fetch the result using fetchall() method.
• Print the result.
• Close the cursor and DB connection.

Let’s gather all instructions in a single program written below and execute it.

import mysql.connector as myconn
mydb = myconn.connect(
    host="localhost",
    user="",
    password="",
    database="journaldev"
)
obj = mydb.cursor()
obj.callproc("lap_details")

for result in obj.stored_results():
    details = result.fetchall()

for det in details:
    print(det)

obj.close()
mydb.close()Code language: Python (python)

In the above program, we have assigned the resultset of the result.fetchall() statement to the details variable. Here, details are a list and stores all the results as a set of list elements. We can iterate through it and get individual rows using a FOR loop.

Note- you can directly print the “result.fetchall()” statement of the first FOR loop. The second FOR loop is used to get the rows in the new line. You can skip the second FOR loop as well.

Below is the result after executing the above program.

Here is another program to call the second stored procedure “lap_by_names”.

import mysql.connector as myconn
mydb = myconn.connect(
    host="localhost",
    user="",
    password="",
    database="journaldev"
)
obj = mydb.cursor()
obj.callproc("lap_by_names", ["lenovo"])

for result in obj.stored_results():
    details = result.fetchall()

for det in details:
    print(det)

obj.close()
mydb.close()Code language: Python (python)

Here, we have passed the second argument to the obj.callproc() method, which is nothing but a stored procedure parameter. We will pass “Lenovo” and then “hp” to get the output. Executing the above program will give us a result as shown below.

As you can see, we have got the result consisting of a model_name “Lenovo” and “hp”.

Conclusion

This is a simple yet powerful tutorial to learn the stored procedures in Python that will help you throughout your Python journey. If you have found this tutorial helpful, don’t forget to share it with your friends and let them learn as well. See you in the following exciting tutorial!

References

MySQL official documentation on Python-MySQL stored procedures.