We can create a user-defined function or stored procedure in a database server like MySQL, MSSQL, PostgreSQL but SQLite does not have a function/stored procedure language.
So CREATE FUNCTION
or CREATE PROCEDURE
does not work in SQLite. In this tutorial, we will learn how to create and redefine SQL functions from Python.
SQLite’s C API allows us to create user-defined functions or redefine the behavior of existing SQL functions or aggregates.
Python sqlite3 module is nothing but a wrapper on this C API, which allows us to create and redefine SQL functions from Python.
Goals of this lesson
In this lesson, you’ll learn:
- Python sqlite3’s
connection.create_function()
to create or redefine functions in SQLite. - The
connection.create_aggregate()
to create or redefine aggregate in SQLite
Also Read:
Table of contents
Defining SQLite Functions using Python
In some situations, we need to perform specific tasks when we are executing a SQL statement, particularly when we are inserting or selecting data. We need to use user-defined functions in a SQL statement.
For example, When displaying the user name, we want it to be in a title case. As you know, SQLite has lots of built-in string functions like LENGTH
, LOWER
, UPPER
, SUBSTR
, REPLACE
, and many more.
Let add a new function TOTITLE
, to convert any string to the title case.
Syntax of create_function()
create_function(name, num_params, func)
This function accepts three arguments.
name
: It is a function name.num_params
: It is the number of parameters the function accepts.func
: It is a Python callable that is called the SQL function within a query.
This function creates a new user-defined function that we can use from SQL statements under the function name.
Note: if num_params is -1, the function may take any number of arguments. connection.create_function() can return any of the types supported by SQLite for example, bytes, str, int, float, and None.
Let’s create a new user-defined function in SQLite using Python.
import sqlite3
def _toTitleCase(string):
return str(string).title()
def getDeveloperName(id):
try:
sqliteConnection = sqlite3.connect('SQLite_Python.db')
cursor = sqliteConnection.cursor()
print("Connected to SQLite")
sqliteConnection.create_function("TOTILECASE", 1, _toTitleCase)
select_query = "SELECT TOTILECASE(name) FROM SqliteDb_developers where id = ?"
cursor.execute(select_query, (id,))
name = cursor.fetchone()
print("Developer Name is", name)
cursor.close()
except sqlite3.Error as error:
print("Failed to read data from sqlite table", error)
finally:
if sqliteConnection:
sqliteConnection.close()
print("sqlite connection is closed")
getDeveloperName(2)
Output:
Connected to SQLite Developer Name is ('Joe',) sqlite connection is closed
Understand Python create SQLite function example
- we created a
_toTitleCase
function that accepts a string as input and converts it into a title case. - Using a
sqlite3.connect()
method, we established a connection to SQLite Database from Python. - Next, we used a
connection.cursor()
method to get a cursor object from the connection object. - Next, we called
create_fundction
of aconnection
class and passed three arguments function name, the number of parameters the_toTitleCase
accepts, and Python callable that is called as the SQL function within a query. - Next, we called the
TOTILECASE
function in SQLite SELECT query to get the developer name in the title case. - Finally, we executed the operation stored in the SQLite SELECT query using a
execute()
method of a Cursor object and fetched the name usingcursor.fetchone()
. - At last, we are closing the cursor and SQLite connection object inside the
finally
block once we complete the operation.
Redefine existing SQLite SQL Functions using Python
In some situations, we need to redefine the working of existing SQLite functions.
For demo purposes, let’s convert the SQLite built-in function LOWER to UPPER case so whenever you invoke this function from SQL query, it will turn the text into the UPPER case instead of the lower case.
For example:
SELECT lower(name) FROM SqliteDb_developers where id = 1
should returnJames
as an output. After our redefinition, it will returnJAMES
.
Let’s create a new definition for lower()
function of SQLite.
import sqlite3
def lower(string):
return str(string).upper()
def getDeveloperName(id):
try:
sqliteConnection = sqlite3.connect('SQLite_Python.db')
cursor = sqliteConnection.cursor()
print("Connected to SQLite")
sqliteConnection.create_function("lower", 1, lower)
select_query = "SELECT lower(name) FROM SqliteDb_developers where id = ?"
cursor.execute(select_query, (id,))
name = cursor.fetchone()
print("Developer Name is", name)
cursor.close()
except sqlite3.Error as error:
print("Failed to read data from sqlite table", error)
finally:
if sqliteConnection:
sqliteConnection.close()
print("sqlite connection is closed")
getDeveloperName(1)
Output:
Connected to SQLite Developer Name is ('JAMES',) sqlite connection is closed
Next Steps:
To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.