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 ;