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.

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

{'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}

DELIMITER ;;

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

BEGIN

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

END;;

DELIMITER ;