Call Stored Procedure using Python


This tutorial will show you how to call stored procedure using Python programming language. Here we 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 opeation on single feature of an application.


Have Python installed in Windows (or Unix)

Pyhton version and Packages

Here I am using Python 3.6.6 version

Example with Source Code

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.

Let’s move on to the example…

Workspace Directory

In the below image you see I have opened a cmd prompt and navigated to the directory where I have to create Python script to call stored procedure using Python.


Please go through below steps in order to complete the example how to call stored procedure using Python.

Creating Python Script

Create below Python script called with below source code. We import the required modules, such as pymysql and password related.

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 we connect to the MySQL database and mask or encrypt the password and using the required user information we call stored procedure using Python code to save into MySQL database table.

import pymysql
from werkzeug import generate_password_hash, check_password_hash

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

Creating MySQL Table

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

CREATE TABLE `tbl_user` (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_email` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`user_id`)

Creating Stored Procedure

Now we will see how to create stored procedure in MySQL database. Here we 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 we first check whether the record with same user name already exists or not. If the same user name exists then we simply return Username Exists !! otherwise we insert the new user information into the database table.


USE `roytuts`$$


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


Testing the Application

When you execute the Python script from cmd prompt, you should see the below screen

call stored procedure using python

Now check in the MySQL database whether user information saved successfully

call stored procedure using python

That’s all. Hope you got idea how to call stored procedure using Python. You may also want to read How to call stored procedure using Codeigniter framework.

Source Code

You can download source code.

Thanks for reading.

Leave a Comment