Calling Python procedure from Python:

Invoking a mysql stored procedure from a Python code holds an immense power of code optimization. This is because if we call a stored procedure from python then it reduces the networks traffic as it carries only the parameter name and values. Therefore using procedure speeds up our execution time.

In this blog, we shall explore Python programs that will call different types of Mysql procedure but make sure that stored procedures are present inside mysql docker container . You can think of this blog is an extension previous blog where we have create multiple stored procedure inside docker contianer. If you have missed the video, you can watch here!

Prerequisites for Mysql procedure from Python:

This is code, we shall have a simple python class that will have one init method. This dunder method will connect mysql database that is present inside the docker container. In otherwords, we shall provide all the credentials like ip address, username, password and database name while invoking the init method. Of course, for all this connecitons we shall import mysql connector.

The Class Definition :

import mysql.connector

class EmployeeInformation:
    def __init__(self):
        try:
            self.empdbconstring = mysql.connector.connect(host="127.0.0.1",user="dev1",password="dev1",database='employees')
            self.cursor = self.empdbconstring.cursor()
        except mysql.connector.Error as err:
            print("Conection failed...")

Calling a stored procedure without any parameter:

The code below is the member function definiton that call a mysql stored procedure that doesn’t take any parameters but returns a set of records!

def getFirstFiftyEmployee(self):
    self.cursor.callproc('getFirstFiftyEmmployees')
    results = []
    for result inself.cursor.stored_results():
        results.append(result.fetchall())
    return results

Calling a stored procedure with only IN parameter:

The second type of calling would be a mysql stored procedure that take only one IN parameter and returns a set of records based on the condition statisfied condition.Therefore, The value inside IN parameter will be the righ hand side operand of where clause of mysql statement

def getEmployeeInformation(self,id):
self.cursor.callproc('getEmployeeInformation',args=(id,))
results = []
for result inself.cursor.stored_results():
    results.append(result.fetchall())
return results

The thrid and last type of stored procedure calling would be a procedure that takes one IN parameter and returns a OUT parameter. According to mysql documention, the OUT parameter is indicated by number zero using thye args tuple’s first index parameter as shown in the code below.

def getEmployeeGender(self,id):
    results = self.cursor.callproc('getEmployeeGender',args = (id,0))
return results[1]

Full Code :

import mysql.connector

class EmployeeInformation:
    def __init__(self):
        try:
            self.empdbconstring = mysql.connector.connect(host="192.168.1.201",user="dev1",password="dev1",database='employees')
            self.cursor = self.empdbconstring.cursor()
        except mysql.connector.Error as err:
            print("Conection failed...")

    def getFirstFiftyEmployee(self):
        self.cursor.callproc('getFirstFiftyEmmployees')
        results = []
        for result inself.cursor.stored_results():
            results.append(result.fetchall())
        return results

   def getEmployeeInformation(self,id):
       self.cursor.callproc('getEmployeeInformation',args=(id,))
       results = []
       for result inself.cursor.stored_results():
           results.append(result.fetchall())
       return results

def getEmployeeGender(self,id):
    results = self.cursor.callproc('getEmployeeGender',args = (id,0))
    return results[1]


obj = EmployeeInformation()
#print( obj.getEmployeeInformation(10004))
print(obj.getEmployeeGender(10004))

Conclusion :

In this article we have explored different ways of calling procedures. Some points to remember, make sure the store procedure is present inside the mysql docker image. The procedure should match in the python code and in mysql database.

In the upcoming section we shall explore dockerfile and how to play with linux docker images.