PYnative

Python Programming

  • Learn Python
  • Exercises
  • Quizzes
  • Code Editor
  • Tricks
Home » Python » Databases » Python Create or Redefine SQLite Functions

Python Create or Redefine SQLite Functions

Updated on: March 9, 2021 | 4 Comments

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:

  • Solve Python SQLite Exercise
  • Read Python SQLite Tutorial (Complete Guide)

Table of contents

  • Defining SQLite Functions using Python
    • Understand Python create SQLite function example
  • Redefine existing SQLite SQL Functions using Python
  • Next Steps:

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.

Filed Under: Python, Python Databases

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

TweetF  sharein  shareP  Pin

About Vishal

Founder of PYnative.com I am a Python developer and I love to write articles to help developers. Follow me on Twitter. All the best for your future Python endeavors!

Related Tutorial Topics:

Python Python Databases

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

  • 15+ Topic-specific Exercises and Quizzes
  • Each Exercise contains 10 questions
  • Each Quiz contains 12-15 MCQ
Exercises
Quizzes

Posted In

Python Python Databases
TweetF  sharein  shareP  Pin

  Python SQLite

  • Python SQLite Guide
  • Python SQLite Insert
  • Python SQLite Select
  • Python SQLite Update
  • Python SQLite Delete
  • Python SQLite Create Functions
  • Python Parameterized Query
  • Python SQLite BLOB
  • Python SQLite DateTime
  • Python Database Exercise

All Python Topics

Python Basics Python Exercises Python Quizzes Python File Handling Python OOP Python Date and Time Python Random Python Regex Python Pandas Python Databases Python MySQL Python PostgreSQL Python SQLite Python JSON

About PYnative

PYnative.com is for Python lovers. Here, You can get Tutorials, Exercises, and Quizzes to practice and improve your Python skills.

Explore Python

  • Learn Python
  • Python Basics
  • Python Databases
  • Python Exercises
  • Python Quizzes
  • Online Python Code Editor
  • Python Tricks

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

  • Twitter
  • Facebook
  • Sitemap

Legal Stuff

  • About Us
  • Contact Us

We use cookies to improve your experience. While using PYnative, you agree to have read and accepted our Terms Of Use, Cookie Policy, and Privacy Policy.

Copyright © 2018–2023 pynative.com