Python – Create or Redefine SQLite Functions
Last Updated :
17 Aug, 2022
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
def _customFun(fstring, dept):
result = 'Welcome ' + fstring + ' your dept is ' + dept
return result
connection = sqlite3.connect( 'geekforgeeks_student.db' )
cursor = connection.cursor()
connection.create_function( "ROHACK" , 2 , _customFun)
sqlQuery = "select ROHACK(First_Name,Department) from \
STUDENT where Student_ID = 1 "
cursor.execute(sqlQuery)
print ( * cursor.fetchone())
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
def length(data):
result = len (data) + 10
return result
connection = sqlite3.connect( 'geekforgeeks_student.db' )
cursor = connection.cursor()
connection.create_function( "length" , 1 , length)
sqlQuery = "select length(First_Name) from STUDENT where Student_ID = 1"
cursor.execute(sqlQuery)
print ( * cursor.fetchone())
cursor.close()
connection.close()
|
Output:
15
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...