Using Python Function with sqlite

Note: You can find the docs in the python doc page http://docs.python.org/library/sqlite3.html#sqlite3.Connection.create_function

This is more of a experience. Not too long ago, I have scrape from the parliament website on profiles of Member's of Parliament, you can find the result here.

The thing is, as I use the data from the sqlite database, I download from the site, I realized that, the Title is part of the name of the MP's. So one would get "XXX , Y.B Tuan". Y.B Tuan is the title.

That would make query like 'select Parti from swdata where Nama=name' hard. Because this is precisely what I am looking at, for another project.

On the other hand, sqlite3 module, apart comes with python standard library since 2.6. Actually have a function called, Connection.create_function.

So I wrote a little function called get_name, and the example show how it works.

import sqlite3 

def get_name(name):
    return name.split(',')[0] 

s = sqlite3.connect('dbname')
# attach the python function
s.create_function('get_name',1,get_name)
# and use it
result = s.execute('select get_name(Nama) from swdata')
print result.next()[0]

Just define a python function,  make sure it return datatype that is compatible with sqlite, attach it with create_function. Now you can use it in your sqlite query in python

Hope this is useful for someone. CHEERS

A little plug, this is something we try to work on in this little group call Sinar Project, and this is still in an early stage