PYnative

Python Programming

Home » Python » Databases » Python Execute MySQL Stored Procedure

Python Execute MySQL Stored Procedure

Updated on:  | 13 Comments

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

Further Reading:

Table of contents

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 ;Code language: Python (python)

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")Code language: Python (python)

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=())Code language: Python (python)

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;Code language: Python (python)

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)Code language: Python (python)

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.

TweetF  sharein  shareP  Pin

About Vishal

I’m Vishal Hule, the Founder of PYnative.com. As a Python developer, I enjoy assisting students, developers, and learners. Follow me on Twitter.

Related Tutorial Topics:

Python Python Databases

All Coding Exercises:

C Exercises
C++ Exercises
Python Exercises

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 25+ questions
  • Each Quiz contains 25 MCQ
Exercises
Quizzes

Comments

  1. Smart eagle says

    I think commit() should be inserted to complete update/save changes in the real database

    like: connection.commit()

    Reply

  2. Reza says

    Hi. thank you for your useful article.
    I have a question, What kind of connection is used here? Service-based or Connection-based only?

    Reply

  3. Edson Bravo says

    boa tarde pessoal como posso usar um procedimento armazenado usando uma entry do tekinter como iria ficar este codigo podem me ajudar?

    Reply

  4. Praj says

    Great content mate!
    Quick question, how can i add triggers such as events?
    I tried JDBC, did not work well so switched to Python in the last moment so I am confused with limited time to finish my assignment.
    So i have a library database and i need to check expired memberships everyday and notify the members. How shall I?
    Thanks

    Reply

  5. rajat says

    If i create 2 tables and want to compare them and the result will store in a new table.How will i do that?

    Reply

    • Vishal says

      Hi Rajat,

      You can write a SQL script for this
      or you can perform all the data comparison in Python for that you need to use data structure effectively

      Reply

  6. dave gardner says

    Hi all, good article though can everyone please note that you will have to run the below:

    mySQL_conn.commit()

    Otherwise, the code will not execute on the server (or commit at least).

    I lost more time on this than i would like to admit : )

    Reply

  7. Steven Hughes says

    Your example is incorrect.
    Calling the procedure add results in ProgrammingError(1064)
    Further your example ode still returns 5,6,0
    What is the line 3,4,7 supposed to represent?
    Please don’t post erroneous material like this..

    Reply

    • Vishal says

      Hey Steven. Thank you for comment.
      We apologizes if issue in this particular code. Give us sometime to check, we will definitely correct if required.

      Reply

    • Vishal says

      Hey Steven example is correct. Tuple entry was by mistake we removed it.
      We already mentioned steps and code in First example.
      In second example to called “add” stored procedure, here we just provided the snippet you should write it in correct code format by referring the above first example.

      Let me know if you still facing problems.

      Reply

      • hank says

        hi all,

        it seems that “add” is a reserved word. I changed the name to add_h and successfully created the procedure.

        Further to the explanation “The stored procedure returns output in result sets, and It is automatically fetched and stored as MySQLCursorBuffered instances.” Please try below…

               args = (5, 6, 0)
       result_args=cursor.callproc('add_h',args) 
       print("###### procedure result  : ",result_args[2])

        Hope this sheds some light on the question.

        Reply

  8. Mohammad Arshad says

    suppose i already created a stored procedure in phpmyadmin
    my question is
    how can i use or access implement in django framework
    and how to get output

    Reply

Leave a Reply

your email address will NOT be published. all comments are moderated according to our comment policy.

Use <pre> tag for posting code. E.g. <pre> Your entire code </pre>

About PYnative

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

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

Explore Python

Coding Exercises

Legal Stuff

We use cookies to improve your experience. While using PYnative, you agree to have read and accepted our:

Copyright © 2018–2026 pynative.com