How To Call MySQL Stored Procedure In Python

This article will show you an example about how to call MySQL stored procedure in python. It will use both the python mysql-connector-python library and the python pymysql library.

1. Call MySQL Stored Procedure In Python Steps.

1. Create two MySQL stored procedures use the below source code.
2. MySQL Stored Procedure add_number.

   USE `dev2qa_example`;
   DROP procedure IF EXISTS `add_number`;
   
   DELIMITER $$
   USE `dev2qa_example`$$
   CREATE PROCEDURE `add_number` (a int, b int, out sum int)
   BEGIN
    set sum = a + b;
   END$$
   
   DELIMITER ;
   

3. MySQL Stored Procedure multiple_number.

   USE `dev2qa_example`;
   DROP procedure IF EXISTS `multiple_number`;
   
   DELIMITER $$
   USE `dev2qa_example`$$
   CREATE PROCEDURE `multiple_number` (a int, b int, out sum int)
   BEGIN
    set sum = a * b;
   END$$
   
   DELIMITER ;

4. Check whether the python mysql-connector-python library and the python pymysql library has been installed on your python environment.
   

   $ pip show mysql-connector-python
   Name: mysql-connector-python
   Version: 8.0.25
   Summary: MySQL driver written in Python
   Home-page: http://dev.mysql.com/doc/connector-python/en/index.html
   Author: Oracle and/or its affiliates
   Author-email: UNKNOWN
   License: GNU GPLv2 (with FOSS License Exception)
   Location: /Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages
   Requires: protobuf
   Required-by: 
   
   *******************************************************************************************
   
   $ pip show pymysql
   Name: PyMySQL
   Version: 1.0.2
   Summary: Pure Python MySQL Driver
   Home-page: https://github.com/PyMySQL/PyMySQL/
   Author: yutaka.matsubara
   Author-email: [email protected]
   License: "MIT"
   Location: /Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages
   Requires: 
   Required-by: 
   

5. If the python mysql-connector-python and the pymysql library is not installed in your python environment, you can run the command pip install pymysql or pip install mysql-connector-python to install them.
6. Call MySQL stored procedure use python mysql-connector-python module example source code.

   import mysql.connector
   
   # get mysql connection object.
   def open_mysql_connection(user=global_user, password=global_password, host=global_host, port=global_port, database=global_db, use_unicode=True):
       conn = mysql.connector.connect(user=user, password=password, host=host, port=port, database=database, use_unicode=use_unicode)
       return conn
   
   # close mysql connection.    
   def close_mysql_connection(conn):
       if conn is not None:
           conn.close()
           conn = None
    
   # call the mysql stored procedure. 
   def call_stored_procedure(conn, stored_procedure_name):
        
       cursor = conn.cursor()
       
       out_args = cursor.callproc(stored_procedure_name, (5, 6, 0))
       
       print(out_args)
       
       print(out_args[0])
       
       print(out_args[1])
        
       print(out_args[2])
           
       conn.commit()
       
       cursor.close()
       
       
   if __name__ == '__main__':
       
       conn = open_mysql_connection()
       
       call_stored_procedure(conn, 'add_number')
       
       call_stored_procedure(conn, 'multiple_number')
       
       close_mysql_connection(conn)

7. Below is the above example output.

   (5, 6, 11)
   5
   6
   11
   (5, 6, 30)
   5
   6
   30
   

8. If you want to use the python pymysql module to call the MySQL stored procedure, the only difference is how to get the MySQL database connection object, when you get the MySQL database connection object use the pymysql module, you can call the above def call_stored_procedure(conn, stored_procedure_name): function to call the MySQL stored procedure also.
9. Below is the source code that can get MySQL database connection object use python pymsql.

   from pymysql import connect, cursors
   
   # get mysql connection object.
   def open_mysql_connection(host='127.0.0.1', user='jerry', password='jerry', db='dev2qa_example', charset='utf8', cursorclass=cursors.DictCursor):
       conn = connect(host=host, user=user, password=password, db=db, charset=charset, cursorclass=cursorclass)
       return conn
       
   # close mysql connection.    
   def close_mysql_connection(conn):
       if conn is not None:
           conn.close()