Python Data Persistence – User Defined Functions
The SQLite database engine by itself is equipped with several built-in functions for finding string length, changing case to upper/lower case, rounding a number, etc. However, it doesn’t have the provision to define a new function with customized functionality. The SQLite module, however, has the provision to do so’ with the help of the create_function () method available to the connection object.
In the following example, we try to represent the price of the product rounded to thousands and attach a ‘k‘ alphabet to it. In other words, 40000 is represented by 40k. First, we define a regular Python function (myfunction) that accepts a number, divides it by 1000, and appends ’k’ to its string conversion. The create_f unction () method has the following prototype:
Example
create_function(SQLFunction, parameters,PythonFunction)
In other words, it assigns a name to the Python function(a function in our case) that can be used as a function in the SQL query.
Example
import sqlite3
conn=sqlite3.connect('mydb.sqlite') def myfunction(num):
return str(round(num/1000))+"k" conn.create_function('priceinK' , 1,myfunction) cur=conn.cursor()
qry="select name, priceinK(price) from products;"
cur.execute(qry)
rows=cur.fetchall( )
print (rows)
conn.close ( )
Output of above code snippet is:
Example
[('Laptop', '25k'), ('TV', '40k'), ('Router', '2k'),
('Scanner', '5k'), ('Printer', '9k'), ('Mobile',
'15k') ]
SQLite also has several built-in aggregate functions such as SUM, AVG, COUNT, etc. to be applied to one or more columns in a table. For example, the query selects SUM (price) from Products’returns sum of values in the price column of all rows. Using the create_aggregate() method defined to be used with the cursor object, it is possible to define a customized aggregate function.
In the following script, a regular Python class named my class s is defined and it contains a step( ) method which is mandatory for the user-defined aggregate function. The step() method increments the count for each product name ending with ‘r\ The create_aggregate () method attaches a name that can be used in the SQL query. When this aggregate function is called, the value returned by finalize () method of the class is in fact the result of the SELECT statement.
Example
import sqlite3
conn=sqlite3.connect('mydb.sqlite')
class myclass:
def__init__(self):
self.count=0
def step(self, string):
if string.endswith('r'):
self.count=self.count+1
def finalize (self) :
return self.count
conn.create_aggregate(1MyF',1,myclass)
cur=conn.cursor()
qry="select MyF(name) from products;"
cur.execute(qry)
row=cur.fetchone()
print ('number of products with name ending with ' r1 : ' , (row) [0] )
conn.close()
The output of the above script is:
Example
number of products with the name ending with : 3