PYnative

Python Programming

  • Learn Python
  • Exercises
  • Quizzes
  • Code Editor
  • Tricks
Home » Python » Databases » Python Execute MySQL Stored Procedure

Python Execute MySQL Stored Procedure

Updated on: March 9, 2021 | 13 Comments

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

Further Reading:

  • Solve Python MySQL Exercise
  • Read Python MySQL Tutorial (Complete Guide)

Table of contents

  • Prerequisites
  • Steps to execute MySQL Stored Procedure in Python
  • Cursor callproc() Method
  • Next Steps:

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: –

How to execute MySQL stored procedure in Python

  1. Connect to MySQL from Python

    Refer to Python MySQL database connection to connect to MySQL database from Python using MySQL Connector module

  2. Get Cursor Object from Connection

    Next, use a connection.cursor() method to create a cursor object. This method creates a new MySQLCursor object.

  3. Execute the stored procedure

    Execute the stored procedure using the cursor.callproc(). here, you must know the stored procedure name and its IN and OUT parameters. For example, cursor.callproc('get_laptop',[1,])

  4. Fetch results

    Once the stored procedure executes successfully, we can extract the result using a cursor.stored_results()

  5. Close the cursor object and database connection object

    use cursor.clsoe() and connection.clsoe() method to close open connections after your work completes.

Execute MySQL stored procedure in python
Execute MySQL stored procedure in python

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.

Filed Under: Python, Python Databases

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

TweetF  sharein  shareP  Pin

About Vishal

Founder of PYnative.com I am a Python developer and I love to write articles to help developers. Follow me on Twitter. All the best for your future Python endeavors!

Related Tutorial Topics:

Python Python Databases

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

  • 15+ Topic-specific Exercises and Quizzes
  • Each Exercise contains 10 questions
  • Each Quiz contains 12-15 MCQ
Exercises
Quizzes

Posted In

Python Python Databases
TweetF  sharein  shareP  Pin

  Python MySQL

  • Python MySQL Connection Guide
  • Python MySQL Insert
  • Python MySQL Select
  • Python MySQL Update
  • Python MySQL Delete
  • Call MySQL Stored Procedure
  • Python MySQL Parameterized Query
  • Python MySQL Transactions
  • Python MySQL Connection Pooling
  • Python MySQL BLOB
  • Python Database Exercise

All Python Topics

Python Basics Python Exercises Python Quizzes Python File Handling Python OOP Python Date and Time Python Random Python Regex Python Pandas Python Databases Python MySQL Python PostgreSQL Python SQLite Python JSON

About PYnative

PYnative.com is for Python lovers. Here, You can get Tutorials, Exercises, and Quizzes to practice and improve your Python skills.

Explore Python

  • Learn Python
  • Python Basics
  • Python Databases
  • Python Exercises
  • Python Quizzes
  • Online Python Code Editor
  • Python Tricks

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

  • Twitter
  • Facebook
  • Sitemap

Legal Stuff

  • About Us
  • Contact Us

We use cookies to improve your experience. While using PYnative, you agree to have read and accepted our Terms Of Use, Cookie Policy, and Privacy Policy.

Copyright © 2018–2023 pynative.com