Calling an user defined function from Python using Pymysql

Overview:

MySQL has several built-in functions like SIN(), COS() and other functions, which can be used as part of an SQL expression.
Similar to such built-in functions, a programmer can define her or his own functions and store them in the MySQL Server.
Functions defined by the users of MySQL Server are called User Defined Functions (UDFs). UDFs are Stored Functions.
User Defined Functions differ from Stored Procedures. While User Defined Functions can be used as part of an SQL expression the Stored Procedures cannot be used as part of SQL expressions.
A user-defined function in MySQL is created using the Create Function statement.
From a Python Program through PyMySQL, a Create Function statement can be executed to create a User Defined Function and subsequently it can be used as part of an SQL expression.

Example:

# ----- Example Python Program for calling a User Defined Function(UDF) in MySQL-----

# import the mysql client for python

import pymysql

# Create a connection object

databaseServerIP = "127.0.0.1" # IP address of MySQL server

databaseUserName = "root" # User name in the MySQL server

databaseUserPassword = "" # Password of the MySQL user

charSet = "utf8mb4" # Character set

cusrorType = pymysql.cursors.DictCursor

mySQLConnection = pymysql.connect(host=databaseServerIP, user=databaseUserName, password=databaseUserPassword, charset=charSet, cursorclass=cusrorType)

try:

# Create a cursor object

cursorInstance = mySQLConnection.cursor()

# SQL query to retrieve the list of UDFs present

sqlQuery = "select ROUTINE_SCHEMA, SPECIFIC_NAME, ROUTINE_TYPE, DATA_TYPE from information_schema.routines where ROUTINE_TYPE='FUNCTION';"

# Execute the SQL Query

cursorInstance.execute(sqlQuery)

# Fetch all the rows

udfList = cursorInstance.fetchall()

print("List of User Defined Functions:")

for udfName in udfList:

print(udfName)

print("-----")

# SQL Query to invoke the UDF with parameter

celsiusValue = 20.51

sqlQuery = "select test.CelsiusToFahrenheit(%s);"%celsiusValue

# Execute the SQL Query

cursorInstance.execute(sqlQuery)

# Fetch all the rows

results = cursorInstance.fetchall()

print("Result of invoking the MySQL UDF from Python:")

for result in results:

print(result)

except Exception as e:

print("Exception occured:{}".format(e))

finally:

cursorInstance.close()

mySQLConnection.close()

Output:

Results of calling an User Defined Function stored in a MySQL Server from a Python program

SQL Statement for creating an User Defined Function in MySQL:

DROP FUNCTION IF EXISTS CelsiusToFahrenheit;

DELIMITER $$

CREATE FUNCTION CelsiusToFahrenheit (Celcius DECIMAL(5,2))

RETURNS DECIMAL(5,2)

BEGIN

DECLARE Fahrenheit DECIMAL(5,2);

SET Fahrenheit = (Celcius * (9.0/5.0)) + 32;

RETURN Fahrenheit;

END; $$

Calling An User Defined Function From Python Using Pymysql

Overview:

Example:

Output:

SQL Statement for creating an User Defined Function in MySQL: