RSS Feed


Comments RSS

Using Python to create a Bible database program

The programming bug bit me this evening and I did something I have wanted to do for a really long time, write my own code to create a useable bible database.   So, I took an indexed bible text available in the public domain, wrote the code to parse it properly and then package the parsed bible into a Sqlite3 database file that can then be querried.  Basically, the heart of any bible search program.  I cannot believe how fast it runs!  That is a whole lot of text, but Python excells in the text processing domain.   The link is to an archive containing the bible text file that gets processed and the Python program.   When run, the database is created from scratch.

The code is Python 2.x style.   You may use this code in anyway you wish. NOTE: the following code is obsolete and has been removed.  Please see this blog post for the fully featured program!

The code has good examples of:
1)reading a text file, line by line
2)text parsing and regular expressions
3)error handling
4)Sqlite3 database handling

Python & SQLite3, demonstrating parameters

Python affords a great opportunity for one to learn basic SQL commands and operations by having SQLite3 built into its standard library.   To learn more, here are some links for Python’s implementation of the API and also the SQLite3 website.

Here is a brief example of  creating a simple SQLite3 database in Python, performing a query against it with parameters.  The use of parameters is demonstrated in 3 ways.

>>> import sqlite3
>>> conn = sqlite3.connect(‘c:\sqlite3\example’)
>>> c = conn.cursor()
>>> c.execute(“””create table contacts(client_id integer primary key, first_name text, last_name text, email text)”””)
>>> c.execute(“””insert into contacts values(NULL, ‘Bob’, ‘Dole’, ‘’)”””)
>>> conn.commit()
>>> c.execute(“””insert into contacts values(NULL, ‘Tom’, ‘Cruz’, ‘’)”””)
>>> conn.commit()

First parameters are passed using the ? placeholder.  The values are supplied by a collection, a list in this example.  A tuple could also be used.

>>>params = [‘Tom’,’Bob’]

>>> c.execute(“””select client_id, first_name, last_name, email from contacts where first_name in (?,?)”””, params)
>>> for i in c:

Second, a mapping object (dictionary) is used to supply the parameters via its keys.   This allows the parameters to be named.

names = {“name1”: ‘Bob’, “name2”:’Tom’}

>>> c.execute(“””select client_id, first_name, last_name, email from contacts where first_name in (name1,name2)”””, names)
>>> for i in c:

Third, we use the built in mapping that stores information about local program variables to supply the parameters.  The locals() function is used to return the mapping information.  This allows the parameters to be simple variables, rather than members of a collection or keys in a mapping.

name1 = ‘Bob’; name2 = ‘Tom’

>>> c.execute(“””select client_id, first_name, last_name, email from contacts where first_name in (name1,name2)”””, locals())
>>> for i in c:

All three methods produce the very same result of the query on the database:

(1, ‘Bob’, ‘Dole’, ‘’)
(2, ‘Tom’, ‘Cruz’, ‘’)
>>> c.close()

Additionally, remember the parameter needs to be passed to the execute() function by a collection or mapping type variable.   So, if you would like to loop through the database, say by the indices, and are using a simple integer variable in the loop, you can take that and convert it to a list variable on the fly (a list containing one element) and use that.    Like this:

out_list = []
for x in range(1,4):
    y = [x]        #here we take the integer x and use it to create the single element list y for use in c.execute()
    c.execute("""select client_id, first_name, last_name, email from contacts where client_id = :y""", y)
    for i in c:

A Blessing

Tonight, while familiarizing myself with SQLite3, I did what I often do when learning a new item or subject, that is I looked into its history and background. I even sought to read the licensing for SQLite. I found that the code for this remarkable database system has been utterly released to the public domain. Instead of a copyright or license, it offers this blessing, which I find very touching and in turn offer it to the kind readers of my blog. This is one of those rare times that the geek realm and spirituality intersect.

May you do good and not evil
May you find forgiveness for yourself and forgive others
May you share freely, never taking more than you give.

Please, also read this interview with Dr. Richard Hipp, the author of the above blessing and inventor of SQLite.,-geek-of-the-week/