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()

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.