Call Stored Procedure using Python

Introduction

This tutorial will show you how to call stored procedure using Python programming language. Here I will use MySQL database server to store the data for this example. You may also want to read How to call stored procedure using Codeigniter framework.

A stored procedure is a set of Structured Query Language (SQL) statements with a given name so it can be reused and shared by multiple programs. It is always better to write a stored procedure when you want to execute multiple SQL statements for performing operation on single feature of an application.

Prerequisites

Python 3.9.0 – 3.9.1, PyMSQL 0.10.1, Password 0.2, Werkzeug 1.0.1, MySQL 8.0.17 – 8.0.22

Preparing Workspace

Preparing your workspace is one of the first things that you can do to make sure that you start off well. The first step is to check your working directory.

When you are working in the Python terminal, you need first navigate to the directory, where your file is located and then start up Python, i.e., you have to make sure that your file is located in the directory where you want to work from.

The root folder of the Python script is python-mysql-stored-procedure. Under this root directory I am going to create Python script for executing the Stored Procedure written in MySQL server.

Install Required Modules

You need to install required modules (if not available already) such as Werkzeug, pymsql and password. You have to execute command pip install PyMySQL to install pymsql module. You need to execute pip install password for installing password module. You need to execute pip install Werkzeug for installing Werkzeug module.

Python Script

Create below Python script called python_sp.py with below source code. You need to import the required modules, such as pymysql and password for connecting to MySQL database server and masking password, respectively.

pymysql module is required to establish the connection with MySQL database and performing queries in MySQL database, such as, calling the stored procedure from Python.

password module is required to mask the password given by user so that no one will be able to see the actual password in the database.

Then you have to connect to the MySQL database and mask or encrypt the password and using the required user information you call stored procedure using Python code to save into MySQL database table.

import pymysql
from werkzeug.security import generate_password_hash, check_password_hash

try:
	conn = pymysql.connect(host='localhost', database='roytuts', user='root', password='root')
	cur = conn.cursor()
	_hashed_password = generate_password_hash('secret')
	cur.callproc('sp_createUser',('Soumitra Roy','contact@roytuts.com',_hashed_password))
	data = cur.fetchall()
	if len(data) == 0:
		conn.commit()
		print('User information saved successfully !')
	else:
		print('error: ', str(data[0]))
except Exception as e:
	print(e)
finally:
	cur.close() 
	conn.close()

MySQL Table

Create a table called tbl_user under roytuts database. Create database roytuts in MySQL server if it does not exist already. I will create a stored procedure to store user data into tbl_user.

CREATE TABLE `tbl_user` (
  `user_id` bigint NOT NULL AUTO_INCREMENT,
  `user_name` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_email` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Stored Procedure

Now I will show you how to create stored procedure in MySQL database. Here I create the stored procedure called sp_createUser with parameters required to store in the above user table. Only the column id is not taken as input parameter because it is the primary key and this column value is auto generated.

Here in this stored procedure I first check whether the record with same user name already exists or not. If the same user name exists then I simply return Username Exists !!, otherwise I insert the new user information into the database table.

DELIMITER $$

USE `roytuts`$$

DROP PROCEDURE IF EXISTS `sp_createUser`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_createUser`(
	IN p_name VARCHAR(45),
	IN p_email VARCHAR(45),
	IN p_password VARCHAR(255)
    )
BEGIN
		IF ( SELECT EXISTS (SELECT 1 FROM tbl_user WHERE user_name = p_name) ) THEN     
			SELECT 'Username Exists !!';		     
		    ELSE		     
			INSERT INTO tbl_user
			(
			    user_name,
			    user_email,
			    user_password
			)
			VALUES
			(
			    p_name,
			    p_email,
			    p_password
			);
		    END IF;
	END$$

DELIMITER ;

Testing the Application

When you execute the Python script from command line tool, you should see the below output screen. If user name exists already then you will see error message otherwise you will see success message:

Now check in the MySQL database whether user information saved successfully:

That’s all. Hope you got idea how to call stored procedure using Python.

Source Code

Download

Roy Tutorials