Let's check some database topics in Python. I was asked to create a simple database to organize wet-lab stuff. No relationships needs, no relational tables required. Just a simple table with determined columns, and a nice GUI to go with it so people can edit, search and use.

My first idea was to use SQLite database, and I stuck with it. After the initial phase of “interviews” to check database requirements, I ended up with a list of tables and decided to start working on the table that organizes the BACs used in the lab. BAC is a DNA vector into which large DNA fragments can be inserted and cloned in a bacterial host, and are used mainly in cytogenetics around here. In the end the table had this structure

CREATE TABLE bac 
(idbac INTEGER PRIMARY KEY,
clone Text,
sdate Date,
source Text,
gene Text, 
chromosome Text,
startpos Integer,
endpos Integer,
antibiotic Text, 
location1 Text, 
temperature Integer, 
tubes Integer, 
box Integer, 
cell Integer, 
dnaex BOOLEAN, 
validation BOOLEAN, 
pcr BOOLEAN, 
projects Text, 
comments Text, 
genelink Text, 
refs Text);

I won't explain in detail each of the fields, but we can see that there is a mix of different types. SQLite doesn't allow many different field types, so we stick to the basics.

And why SQLite? The module to access it comes with Python 2.5, the whole database is stored in one file that can be moved around and it allows a full SQL query language, which is perfect for these simple cases. So we will going to use Python, SQLite and wxPython to create a simple application to manage our simple database.

Let's continue coding our small Python + SQLite application. The initial idea was to have a file for the interface and another file for the DB access. We will start with the later. If you have access to the repository you will see two Python files, bac_form.py and db_obj.py. At the moment they are not well commented and have some junk lines at the bottom, legacy from older versions. Take a look on

db_obj.py

.

It has two class declarations, one called

DB_Generic

and another one called Bac. Remember in the last post where I mentioned that the idea was to have different simple tables in the same SQLite database and each table would have a simple input/output interface (If I didn't mention that, I just did!). So, we can create a generic DB access class and we can subtype from it for every table that we will be using. In the db_obj.py file we have at the moment the generic database management class, a class derived from the generic to access the Bac database and an initialization function, that opens the access to the SQLite file. Let's take a look at it:

def link_db():
    '''initializes the database file'''
    try:
        db = sqlite3.connect("samples.db")
    except sqlite3.Error, errmsg:
        print 'DB not available ' + str(errmsg)
        sys.exit()
 
    db_cursor = db.cursor()
    return db_cursor, db

In order to access a SQLite database file we need initially the name of the file. After importing sqlite3 (we're using the latest version of SQLite here) Python has everything it needs to access, change and manipulate data in a SQLite database. Just to be sure the database file is there and we don't get an error, we have the initialization code inside an exception. We have seen exceptions before and in this case we use it to be sure the database file has been accessed with no problems. The exception structure looks like

try:
	#here we try to do something
	#the code placed here would be executed
	#if no error reported it will go until the end and exit
	#if not, some error (exception) raised
except:
	#the code under except will be executed

So, the first step is to connect to the database file

db = sqlite3.connect("samples.db")

In our case it's a fixed file, but the connect method receives any kind of string. It could have been some parameter obtained from the command line or a string from a configuration file. If the connect is successful, no error will be raised and we are safe to continue. We connected to database, now what? We need a cursor, an object that will actually access the data and allow us to execute SQL commands on it.

db_cursor = db.cursor()

cursor method works on the database connection object that we created previously. We're set. This function returns the cursor and database connection objects that we created, in a tuple and this function can be called from the classes we are going to work. The classes will then have connection to the database and a cursor that would manage, select, delete and add data to it.