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
Preparing your 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…
In the below image you see I have opened a cmd prompt and navigated to the directory where I have to create Python script for building XML using Python.
Please go through below steps in order to complete the example how to call stored procedure using Python.
Step 1. Create below Python script called python_sp.py with below source code. We import the required modules, such as pymysql and password related. pymysql is required to establish the connection with MySQL database and performing queries in MySQL database, such as, calling the stored procedure from Python. password related 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 try: 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','firstname.lastname@example.org',_hashed_password)) data = cur.fetchall() if len(data) is 0: conn.commit() print('User information saved successfully !') else: print('error: ', str(data)) except Exception as e: print(e) finally: cur.close() conn.close()
Step 2. 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`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Step 3. 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.
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 cmd prompt, you should see the below screen
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. You may also want to read How to call stored procedure using Codeigniter framework.
Thanks for reading.Tags: MySQL Stored Procedure