How To Define Custom SQLite Function And Redefine Existing SQLite Function In Python

This article will show you examples of how to define custom SQLite functions in python, it will also tell you how to redefine SQLite database built-in functions to overrides its original function.

1. How to define custom SQLite functions in python.

1. Define a Python function that will be invoked when calling the function in SQLite SQL statement.
2. Create an SQLite3 database connection object.
3. Call the above SQLite3 database connection object’s method create_function(func_name, func_parameter_number, python_function_object) to create a SQLite database function object. The create_function method has three parameters.
4. The first parameter is the user-defined SQLite function name that will be used in the SQLite SQL statement.
5. The second parameter is the user-defined SQLite function parameter number.
6. The third parameter is the python function object that is defined in step 1, this function will be executed when the user-defined function is called in the SQLite SQL statement.

2. How to redefine SQLite built-in functions in python.

1. It is similar to create a user-defined SQLite function in python, you can override SQLite built-in functions with the python SQLite3 connection object create_function also, but there are some tips that you should take care of.
2. When you redefine the SQLite built-in function with the python create_funciton method, the first parameter should be the SQLite3 built-in function name for example lower, upper etc.

3. How to define or redefine SQLite functions in python example.

1. This example contains two functions define_custom_func() which define a SQLite function in python and redefine_existing_func() which override the SQLite built-in lower() function.
   

   import sqlite3
   
   db_name = 'test-sqlite.db'
   
   table_name = 'user_account'
    
   ''' 
   This function is the target python function for the SQLite user defined function.
   
   It will reverse the input string order and return the reversed string.
   
   ''' 
   def reverse_str(str):
       
       ret = str[::-1]
       
       return ret
   
   '''
   This function will create a custom SQLite function in python source code.
   '''
   def define_custom_func():
       
       # Connect to SQLite3 database.
       conn = sqlite3.connect(db_name)
       
       # Define the user defined SQLite3 function name string is 'decode' and parameter number is 1.
       func_name = 'decode'
       func_param_number = 1
           
       # Create the SQLite3 user defined function, please note the third parameter is the target python function object not the target python function name string.   
       conn.create_function(func_name, func_param_number, reverse_str)
          
       # Get the connection cursor object.    
       cursor = conn.cursor()
       
       # Call the decode function in the insert statement.
       insert_sql_str = 'insert into user_account values(null, ?, decode(?), ?)'
       
       insert_value_tuple = ('jerry', '13579','[email protected]')
       
       # Execute the insert statement.
       cursor.execute(insert_sql_str, insert_value_tuple)
           
       conn.commit()
       
       cursor.close()
       
       conn.close()    
       
    
   '''
   The target python function for SQLite built-in function lower().
   ''' 
   def lower_to_upper(str):
       
       if str != None:
           ret = str.upper()
       else:
           ret = ""   
       
       return ret
   
   
   '''
   Redefine the SQLite built-in function lower() in python example. 
   '''
   def redefine_existing_func():
       
       conn = sqlite3.connect(db_name)
       
       # Redefine the SQlite buit-in lower function, so when your call it in sql statement, it will execute the python function lower_to_upper in fact.
       conn.create_function('lower', 1, lower_to_upper)
           
       cursor = conn.cursor()
       
       # Call the lower() function in the sql statement.
       query_sql_str = 'select lower(user_name) from user_account'
       
       cursor.execute(query_sql_str)
       
       name_list = cursor.fetchall()
       
       print(name_list)
           
       conn.commit()
       
       cursor.close()
       
       conn.close()         
   
   
   if __name__ == '__main__':
       
       # define_custom_func()
       
       redefine_existing_func()