Executing Stored Procedure Using Pymysql

Overview:

  • A stored procedure is a routine written in the SQL dialect supported by the RDBMS.  
  • MySQL supports stored procedures.
  • PyMySQL supports executing stored procedures from a Python program.
  • Advantages of using a stored procedure:
    • Consolidating relevant business logic as a routine that is stored in the Database
    • Storing the routine in the database server itself, rather than a set of interleaved SQL statements embedded in a client program.
    • A set of stored procedures makes a business API and provides modular access to the business logic.
    • The data and the code are co-located in the same database server thus reducing significant network overhead and improving latency of the application programs.
    • The stored procedures can be access-controlled only for a set of database users.
  • A stored procedure differs from a User Defined Function or a Stored Function as the stored procedure cannot be used as part of an SQL expression, as it does not return any value.

 

Example:

# import the python mysql client

import pymysql

 

# Create a connection object to the MySQL Database Server

hostName        = "127.0.0.1"

userName        = "root"

userPassword    = ""

databaseName    = "test"

databaseCharset = "utf8mb4"

cusrorType      = pymysql.cursors.DictCursor

 

databaseConnection   = pymysql.connect(host=hostName,

                                       user=userName,

                                       password=userPassword,

                                       db=databaseName,

                                       charset=databaseCharset,

                                       cursorclass=cusrorType)

                                    

try:

    # Cursor object creation

    cursorObject    = databaseConnection.cursor()                                    

 

    # Execute the sqlQuery

    cursorObject.execute("call Top10Students()")

 

    # Print the result of the executed stored procedure

    for result in cursorObject.fetchall():

        print(result)

 

except Exception as e:

    print("Exeception occured:{}".format(e))

 

finally:

    databaseConnection.close()

 

Output:

{'id': 2, 'firstname': 'Jefferson', 'lastname': 'Thomas', 'courseid': 154, 'score': 90}

{'id': 3, 'firstname': 'Ada', 'lastname': 'Lovelace', 'courseid': 154, 'score': 85}

{'id': 1, 'firstname': 'John', 'lastname': 'Adams', 'courseid': 154, 'score': 80}

 

The Stored Procedure:

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` PROCEDURE `Top10Students`()

BEGIN

  SELECT * from test.Student order by score desc limit 3;

END;;

DELIMITER ;

  

     


Copyright 2020 © pythontic.com