ExampleEdit & Run
import sqlite3

def func_to_register(args):
  ...

with sqlite3.connect('example.db') as conn:
  #register the function
  conn.create_function('func_name', 1, func_to_register)
  ...
  cursor.execute('SELECT func_name(a), func_name(b) FROM example;')
  ...
 Visualize 
copy

 

sqlite3 module provides a Python interface to use and manage SQLite database.  In this article we will look on how we can use Python functions in SQL statements when querying an SQLite database. 

SQL allows function to be used in statements to process data before it is returned. Particularly, SQL functions are allowed either in the WHERE clause of the SELECT statement or in column lists.

To allow Python functions to be used in SQL queries, we will need to register that function using the create_function() method of connection objects.

Basic example

Consider if we have an SQLite database for storing student details. The following example creates the students database:

ExampleEdit & Run
import sqlite3

with sqlite3.connect('students.db') as conn:
  try:
    conn.execute(
      '''
        CREATE TABle student (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          fname TEXT,
          lname TEXT,
          college TEXT
          );
      ''')
    print('Table student created Successfully')
    conn.executescript('''
        INSERT INTO student(fname, lname, college) VALUES ('john', 'doe', 'havard');
        INSERT INTO student (fname, lname, college) VALUES ('jane', 'smith', 'reeds');
        INSERT INTO student (fname, lname, college) VALUES ('mary', 'gates', 'havard');
      ''')
    print('Rows Inserted Successfully.')
  except ZeroDivisionError:
    print('Something went wrong!')
 Visualize 
copy
Output:
Table student created Successfully
Rows Inserted Successfully.
ExampleEdit & Run

retrieve data:

import sqlite3

with sqlite3.connect('students.db') as conn:
  cursor = conn.cursor()
  cursor.execute('SELECT fname, lname, college FROM student;')
  for row in cursor:
    print(row)
 Visualize 
copy
Output:
('john', 'doe', 'havard')
('jane', 'smith', 'reeds')
('mary', 'gates', 'havard')

Register a function

Consider if we want to retrieve the students from the database with the student names being capitalized and the name of school in upper case and without updating the columns in database. We can define and register two functions as shown below.

ExampleEdit & Run
import sqlite3

def transform_upper(s):
  return s.upper()

def capitalize(s):
  return s.capitalize()

with sqlite3.connect('students.db') as conn:
  #register the functions
  conn.create_function('transform_upper', 1, transform_upper)
  conn.create_function('capitalize', 1, capitalize)

  cursor = conn.cursor()
  cursor.execute('SELECT capitalize(fname), capitalize(lname), transform_upper(college) FROM student;')
  for row in cursor:
    print(row)
 Visualize 
copy
Output:
('John', 'Doe', 'HAVARD')
('Jane', 'Smith', 'REEDS')
('Mary', 'Gates', 'HAVARD')

In the above example we defined and registered two functions transform_upper and capitalize, we then used the functions in the SELECT query to capitalize the student names and transform the name of the school to uppercase.  Of course, SQL provides native functions for basic operations such as UPPER to capitalize a value.

The conn.create_function() creates an sql function from a Python function. Its basic syntax is shown below:

Syntax: 
conn.create_function(name, nargs, func)
copy

The name parameter is the name of the function as it will appear in the sql queries.

The nargs parameter is the number of arguments taken by the function.

The func parameter is the actual Python function.