Beginning Python for Bioinformatics

Let’s get back to our SQLite and wxPython project. We haven’t seen anything on wxPython yet, and we will check the interface only on the next post. For now, let’s see some extra code added to the SQLite access class. Remember that we have a generic class and one class derived from it that would work on accessing specific tables in our database file.

When we last covered the db access routines, there was no search for an entry (the function returned everything in the table no matter what), there was no update function in case someone would want to modify an entry and there was no delete method if you wanted to delete something. In the meantime, I added all of this functionality (and some other) to the generic class and extended it to the class derived from it. Let’s check how the generic class is now (you will notice that there is an issue in one of the methods, if someone can help me I’d appreciate. More details later.)

class DB_Generic():
    '''generic class to add DB functionality'''
    def __init__(self, table_name, db_path = ''):
        #par= name of the table to be used
        self.table_name = table_name
        if len(db_path) > 0:
            self.db_path = db_path
            print db_path

    def get_data_generic(self, range = 1, bac_to_get = 0):
        '''gets the data from the database'''       

        if sys.platform == 'darwin':
            (cursor, database) = link_db(self.db_path)
        else:
            (cursor, database) = link_db()

        if range == 1:
            cursor.execute("""SELECT * FROM %s""" % self.table_name)
        elif range == 2:
            cursor.execute("""SELECT * FROM %s where idbac = %d""" % (self.table_name, bac_to_get))

        table_data = cursor.fetchall()
        raw_data = []
        for i in table_data:
            raw_data.append(list(i))

        self.table_data = raw_data
        database.close()

    def insert_data(self, values_list, insert_string):
        '''inserts data in the database'''

        if sys.platform == 'darwin':
            (cursor, database) = link_db(self.db_path)
        else:
            (cursor, database) = link_db()

        cursor.execute(insert_string % self.table_name, values_list)

        database.commit()
        database.close()

    def update_data(self, values_list):
        '''edits and updates fields'''

        if sys.platform == 'darwin':
            (cursor, database) = link_db(self.db_path)
        else:
            (cursor, database) = link_db()

        #change this to generic!!!!!!!!!!!!
        cursor.execute("UPDATE bac SET  projects = ?, comments = ?, temperature = ?, cell = ?, box = ?, tubes = ?, chromosome = ?, sdate = ?, clone = ?, source = ?, location1 = ?, startpos = ?, endpos = ?,
		gene = ?, genelink = ?, dnaex = ?, validation = ?, pcr = ?, refs = ?, antibiotic = ? WHERE idbac = ?",
        (values_list['projects'], values_list['comments'], values_list['temperature'], values_list['cell'], values_list['box'], values_list['tubes'],
         values_list['chromo'], values_list['date'], values_list['clone'], values_list['source'], values_list['location'], values_list['start'], values_list['end'],
         values_list['gene'], values_list['genelink'], values_list['dna'], values_list['validation'], values_list['pcr'],
         values_list['refs'], values_list['antibiotic'], values_list['idbac']))

        database.commit()
        database.close()

    def delete_data(self, delete_string):
        '''deletes one field'''

        if sys.platform == 'darwin':
            (cursor, database) = link_db(self.db_path)
        else:
            (cursor, database) = link_db()
        cursor.execute(delete_string)

        database.commit()
        database.close()

In the next couple of posts we’ll dissect each function and see what’s going on. The class definition wasn’t changed, so we start with get_data_generic

def get_data_generic(self, range = 1, bac_to_get = 0):
	'''gets the data from the database'''       

	if sys.platform == 'darwin':
		(cursor, database) = link_db(self.db_path)
	else:
		(cursor, database) = link_db()

	if range == 1:
		cursor.execute("""SELECT * FROM %s""" % self.table_name)
	elif range == 2:
		cursor.execute("""SELECT * FROM %s where idbac = %d""" % (self.table_name, bac_to_get))

	table_data = cursor.fetchall()
	raw_data = []
	for i in table_data:
		raw_data.append(list(i))

	self.table_data = raw_data
	database.close()

The first difference we notice here is the sys.platform usage. This is required if we intend to package our application as an OS X app, using py2app. When a Python/wxPython application is packaged in OS X, the actual application executable is inside the a directory named after the application (or whatever you set up). In our case here we don’t provide a way for the Python script to receive the path and name for the database on a command line, as we expect it to be in the executable’s current directory. Because of that we need to provide a “config” file (in our case here a one-line text file with the database path) inside the application wrapper, something we will see in the end of the series.

Another modification here is the range parameter and the addition of the bac_to_get parameter. Notice that both parameters have a value assigned to it. This means that they are optional, the function’s call can pass them or not. If it doesn’t pass, their value will be the one assigned on the function declaration. So, here if we are interested in getting all bacs, range will have the value of 1 and we don’t need to worry about it. If we want an specific bac we will pass range as 2 and then pass the bac_to_get ID to be returned.

A final change/addition is that we added a new select statement for the cases when range equals 2. This time we are adding the bac ID to be returned.

Previously in the series:
Part 1
Part 2
Part 3
Part 4
Part 5