Python – Create or Redefine SQLite Functions

The SQLite does not have functions or stored procedure language like MySQL. We cannot create stored functions or procedures in SQLite. That means the CREATE FUNCTION or CREATE PROCEDURE does not work in SQLite. In SQLite instead of CREATE FUNCTION or CREATE PROCEDURE we have SQLite’s C API which allows us to create our own user-defined functions, or we can redefine the existing SQL functions by taking the names of predefined functions. 

To use SQLite’s C API we don’t need any special module to import the python sqlite3 module contains this C API, which allows us to create and redefine SQL functions in Python.

The table used here can be created using this article- here. 

Create a new user-defined function

Sometimes, we need to perform specific tasks multiple times, so it is always better to create a set of tasks known as a function such that when we are executing an SQL statement we can directly call that function. In this case, we need to create our own function, called user-defined functions.

To create a user defined function in python SQLite create_function() is used.

Syntax :

create_function(name, no_of_params, func)

Parameter:

• name: The function name we want to give
• no_of_params: The number of parameters the function accepts. If no_of_params is set to -1, the function may take any number of arguments. 
• func: It is a Python callable function that is called the SQL function within a query.

Returns:

create_function() can return bytes, str, int, float, and None any of the types supported by SQLite.

First, create a user-defined function and then call create_function() which is a connection class and simply pass the three arguments provided in the above syntax- function name, the number of parameters the ‘_customFun’ accepts, and Python callable that is called as the SQL function within a query. After execution of the operation and fetching the result using cursor.fetchone(). We get our user-defined functions desired output.

Example:

Python3

import sqlite3

# define user defined function
def _customFun(fstring, dept):
    result = 'Welcome ' + fstring + ' your dept is ' + dept
    return result


# define connection and cursor
connection = sqlite3.connect('geekforgeeks_student.db')
cursor = connection.cursor()

# create the user defined function
connection.create_function("ROHACK", 2, _customFun)

# create and execute sql query
sqlQuery = "select ROHACK(First_Name,Department) from \
STUDENT where Student_ID = 1"
cursor.execute(sqlQuery)
print(*cursor.fetchone())

# close cursor and connection
cursor.close()
connection.close()

Output:

Welcome Rohit your dept is IT

To redefine existing SQLite functions

In some cases, we need to redefine the existing working of SQLite functions. For example, let’s change the SQLite built-in function ‘length()’ so whenever you invoke this function from SQL query, it will count the length of the string and add 10 to that count instead of just giving the normal count. 

Example:

Python3

import sqlite3

# re-define existing SQLite function with
# new definition
def length(data):
    result = len(data) + 10
    return result


# define connection and cursor
connection = sqlite3.connect('geekforgeeks_student.db')
cursor = connection.cursor()

# create the function with same name as existing function
connection.create_function("length", 1, length)

# create and execute sql query
sqlQuery = "select length(First_Name) from STUDENT where Student_ID = 1"
cursor.execute(sqlQuery)
print(*cursor.fetchone())

# close cursor and connection
cursor.close()
connection.close()

Output:

15