Beginning Python for Bioinformatics

Thanks to the comments and suggestions to the last post, it’s possible to make now a more pythonic and clearly generic database update class. Let’s check how the “generic” update/edit entry function is currently:

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()

    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()

which is really ugly and, although it works, is not really useful outside this small project. Based on the comments the best option was to use placeholders and a dictionary, similar to the approach used on the insert data function. Pre-formatting a string to have both the field name to be updated and a placeholder (for instance :idbac) that will receive the values

update = ','.join(['%s=:%s' % (y, y) for y in values_list])

where update is the string we want and values_list is the dictionary with all the key-value pairs. I tried this approach, using this structure in the generic function, but then I decided that the best alternative was to put this join in the derived class function and pre-populate the string with the values and then send this string directly to the update function. In the end I opted to use this

update = ','.join(['%s=\"%s\"' % (y, values_list[y]) for y in values_list])

The latter is slightly different to what was suggested. The original one would create a tuple with the keys from the dictionary, making for instance sdate:sdate. With all these place holders just pass the dictionary and you have all the values inserted. This would be handy if the insert string was being created on the “generic” function. If we move this to the derived class, we can use the the alternative, keeping in mind that the values parsed should be surrounded by quotes, otherwise the SQL UPDATE statement will have problems with spaces and other foreign characters that should not be there. So instead of placeholders we will have gene:"PTEN" and we can attache this joined string to the actual commands. We then can move all the machinery from the “generic” function that can be written as

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

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

    database.commit()
    database.close()

That’s it, very elegant (we will see the derived class in the next post). And finishing our generic class, we would need a delete function, so the user can eliminate entries that he/she doesn’t want anymore. It’s also a very simple function

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()

We will check the delete string next time. Again, I would like to thank for all the comments, it has been really helpful for me.

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