Python Create or Redefine SQLite Functions

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

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 a connection 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 using cursor.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 return James as an output. After our redefinition, it will return JAMES.

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.