Perl for Bioinformatics: Day 2 – querying a database (DBI)

So in Day 1, we learned how to use Perl to parse a file. Today we are going to learn how to extract information from a database.

A database is an organised collection of data. Since lots of Bioinformatics resources store their data in a database, it’s pretty useful to find out early on how to go about using them.

There are lots of different types of databases (e.g. MySQL, PostgreSQL, Oracle) and each of them has slight differences in the way that you interect with them. To make life easier, the good people of Perl have written a library called DBI that provides a common way of accessing them (feel free to have a good look around the DBI documentation on CPAN and come back when you’re ready).

Accessing a database with DBI

The following script provides a very simple example of how you might go about using DBI libary to extract data from your database. We are extracting OMIM data from one of our local Oracle databases, but you should be able to see how it can be extended to your own situation.

Note: you’ll need to ask your database administrator for suitable values to replace ‘??????’

#!/usr/bin/env perl

use strict;
use warnings;

use DBI;

# information that we need to specify to connect to the database
my $dsn         = "dbi:Oracle:host=?????;sid=?????";  # what type of database (Oracle) and where to find it (sinatra)
my $db_username = "?????";                            # we connect as a particular user
my $db_password = "?????";                            # with a password

# connect to the database
my $gene3d_dbh = DBI->connect( $dsn, $db_username, $db_password )
	or die "! Error: failed to connect to database";

# this is the query that will get us the data
my $omim_sql = <<"_SQL_";
SELECT
	OMIM_ID, UNIPROT_ACC, RESIDUE_POSITION, NATIVE_AA, MUTANT_AA, VALID, DESCRIPTION, NATIVE_AA_SHORT
FROM
	gene3d_12.omim
WHERE
	valid = 't'
_SQL_

# prepare the SQL (returns a "statement handle")
my $omim_sth = $gene3d_dbh->prepare( $omim_sql )
	or die "! Error: encountered an error when preparing SQL statement:\n"
		. "ERROR: " . $gene3d_dbh->errstr . "\n"
		. "SQL:   " . $omim_sql . "\n";

# execute the SQL
$omim_sth->execute
	or die "! Error: encountered an error when executing SQL statement:\n"
		. "ERROR: " . $omim_sth->errstr . "\n"
		. "SQL:   " . $omim_sql . "\n";

# go through each row
while ( my $omim_row = $omim_sth->fetchrow_hashref ) {
	printf "%-10s %-10s %-10s %-10s %-10s %s\n",
		$omim_row->{OMIM_ID},
		$omim_row->{UNIPROT_ACC},
		$omim_row->{RESIDUE_POSITION},
		$omim_row->{MUTANT_AA},
		$omim_row->{NATIVE_AA},
		$omim_row->{DESCRIPTION}
		;
}

This prints out:

100650     P05091     504        LYS        GLU        ALCOHOL SENSITIVITY - ACUTE ALCOHOL DEPENDENCE - PROTECTION AGAINST - INCLUDED;; HANGOVER - SUSCEPTIBILITY TO - INCLUDED;; SUBLINGUAL NITROGLYCERIN - SUSCEPTIBILITY TO POOR RESPONSE TO - INCLUDED;; ESOPHAGEAL CANCER - ALCOHOL-RELATED - SUSCEPTIBILITY TO - INCLUDED ALDH2 - GLU504LYS (dbSNP rs671)
100690     P02708     262        LYS        ASN        MYASTHENIC SYNDROME - CONGENITAL - SLOW-CHANNEL CHRNA1 - ASN217LYS
100690     P02708     201        MET        VAL        MYASTHENIC SYNDROME - CONGENITAL - SLOW-CHANNEL CHRNA1 - VAL156MET
...

Improvements

The first thing to notice was that this was quite a lot of typing: writing out the SQL, setting up database handles/statement handles, checking return values, printing out decent error messages, etc. Lots of typing means lots of code to maintain and far more chance of repeating yourself (which you really shouldn’t be doing).

When faced with the prospect of lots of typing, any decent (i.e. lazy) programmer will be instantly thinking about how they can avoid it: what shortcuts they can make, what libraries they can reuse. As luck would have it the good people of Perl have already thought of this and come up with DBIx::Class which will be the basis of a future post.

Discussion

There is a lot of value in understanding how raw DBI works. However, when you start writing and maintaining your own code, there is a huge amount of value in using a library (such as DBIx::Class) that builds on DBI and helps to keep you away from intereacting with DBI directly.

Perl for Bioinformatics: Day 1 – parsing a file

You don’t have to hang around too long in a Bioinformatics lab before someone asks you to parse data from a <insert your favourite data format here> file. Since we’ve just had some people join the lab who are new to coding – parsing a file seemed a good place to start.

The following is intended as a “Day 1” introduction to a typical Bioinformatics task in Perl.

Caveats

Some things to take into account before we start:

  1. It’s very likely that somebody, somewhere has already written a parser for your favourite data format. It’s also likely that they’ve already gone through the pain of dealing with edge cases that you aren’t aware of. You should really consider using their code or at least looking at how it works. If you’re writing in Perl (and in this case, we are) then you should have a rummage around CPAN (http://www.cpan.org) and BioPerl (http://www.bioperl.org).
  2. The following script is not intended as an example of “best practice” code – the intention here is to keep things simple and readable.

Getting the data

Okay so it’s our first day and we’ve just been asked to do the following:

Parse “genemap” data from OMIM

Err.. genemap? OMIM? If in doubt, the answer is nearly always the same: Google Is Your Friend.

Googling “download OMIM” get us what we want. Now we have just have to read the instructions, follow the instructions, fill in the forms, direct your web browser at the link that gets sent in an email, download the data via your web browser.

If you get stuck, don’t be afraid to ask – either the person sitting next to you or by emailing the “contact” section of the website you’re using. However, also remember that you are here to do research – and a lot of that comes down to rummaging around, trying to figure stuff out for yourself.

It’s really useful to keep things tidy so we’re going to create a local directory for this project by typing the following into a terminal (note: lines that start with ‘#’ are comments, stuff that comes after the ‘>’ are linux commands).

# go to my home directory
> cd ~/

# create a directory that we're going to work from
> mkdir omim_project

# move into to this new directory
> cd omim_project

# create a directory for the data 
# note: the date we downloaded the data will definitely be useful to know
> mkdir omim_data.2014_09_16

# look for the files we've just downloaded
> ls -rt ~/Downloads

# copy the ones we want into our data directory
> cp ~/Downloads/genemap ./omim_data.2014_09_16

Step 1. Setting up the script

Now we can write our first Perl script which is going to parse this file – i.e. extract the data from the text file, organise the data into a meaningful structure, output the information we need.

There are loads of different text editors you can use – I’m assuming you have access to ‘kate’.

# open up 'kate' with a new file for our script called 'parse_genemap.pl'
> kate parse_genemap.pl

Here’s the first bit of code – we’ll go through it line by line.

#!/usr/bin/env perl

use strict;
use warnings;

use File::Basename qw/ basename /;

# 'basename' is imported from File::Basename
my $PROGNAME = basename( $0 );

my $USAGE =<<"_USAGE";
usage: $PROGNAME <genemap_file>

Parses OMIM "genemap" file

_USAGE

my $genemap_filename = shift @ARGV or die "$USAGE";

Line 1 (called ‘hashbang’) tells the linux terminal that we want this file to be run as a Perl script.

#!/usr/bin/env perl

The next commands make sure that we find out straight away if we’ve made any mistakes in our code. It’s generally a good thing for our programs to “die early and loudly” as soon as a problem happens. This makes debugging much easier when things get more complicated.

use strict;
use warnings;

The following command imports a function ‘basename’ that we’ll use to get the name of the current script.

use File::Basename qw/ basename /;

Note: you can find out lots more about what a module does by entering the following into a terminal:

perldoc File::Basename

Perl put lots of useful variables into special variables. To get the full path of the script we are currently running, we can use ‘$0’.

This is what Perl’s documentation pages have to say about it:

$0
Contains the name of the program being executed.

Feeding this into ‘basename’ will take the directory path off the script and just leave us with the script name (i.e. ‘parse_genemap.pl’). This is handy when we want to provide a simple note on how this script should be run.

# 'basename' is imported from File::Basename
my $PROGNAME = basename( $0 );

my $USAGE =<<"_USAGE";
usage: $PROGNAME <genemap_file>

Parses OMIM "genemap" file

_USAGE

Step 2. Gather data from the command line

We’ve set this program up to take a single argument on the command line which will be the location of the ‘genemap’ file to parse. This gives us some flexibility if we want to parse different genemap files, or if the genemap files are likely to move around in the file system.

The arguments on the command line are stored in another special variable called ‘@ARGV’. The ‘@’ symbol means this is an array (or set of values) rather than a single value. We’ll use the built-in function ‘shift’ to get the first command line argument from that list.

my $genemap_filename = shift @ARGV or die "$USAGE";

If the list is empty then it means we’ve run the script without any arguments. If this happens we want to end the progam with a useful message on what the script is doing and how it should be run.

Step 3. Reading the data

The following creates a “file handle” that can be used for reading and writing to a file. There are lots of ways of creating file handles in Perl (I suggest looking at ‘Path::Class’).

# create a file handle that we can use to input the contents of
# the genemap file
# (and complain if there's a problem)
# note: '<' means "input from this file" in linux shells

open( my $genemap_fh, '<', $genemap_filename )
or die "! Error: failed to open file $genemap_filename: $!";

Again, if there’s a problem (e.g. the file we are given doesn’t exist) then we want to know about it straight away with a sensible error message.

Now we are going to read the file line-by-line and create a data structure for each row. Most of the following code is just made up of comments.


# create an array that will contain our genemap entries
my @genemap_entries;

# go through the file line by line
while( my $line = $genemap_fh->getline ) {

  # an example line from file 'genemap' looks like:
  # 1.1|5|13|13|1pter-p36.13|CTRCT8, CCV|P|Cataract, congenital, Volkmann type||115665|Fd|linked to Rh in Scottish family||Cataract 8, multiple types (2)| | ||

  # the keys for each column are specified in 'genemap.key':
  # 1  - Numbering system, in the format  Chromosome.Map_Entry_Number
  # 2  - Month entered
  # 3  - Day     "
  # 4  - Year    "
  # 5  - Cytogenetic location
  # 6  - Gene Symbol(s)
  # 7  - Gene Status (see below for codes)
  # 8  - Title
  # 9  - Title, cont.
  # 10 - MIM Number
  # 11 - Method (see below for codes)
  # 12 - Comments
  # 13 - Comments, cont.
  # 14 - Disorders (each disorder is followed by its MIM number, if
  #      different from that of the locus, and phenotype mapping method (see
  #      below).  Allelic disorders are separated by a semi-colon.
  # 15 - Disorders, cont.
  # 16 - Disorders, cont.
  # 17 - Mouse correlate
  # 18 - Reference

  # split up the line based on the '|' character
  # note: we use '\|' since writing '|' on its own has a special meaning
  my @cols = split /\|/, $line;

  # create a HASH / associative array to provide labels for these values
  # note: arrays start from '0' so we take one away from the columns mentioned above
  my %genemap_entry = (
    id                 => $cols[0],
    month_entered      => $cols[1],
    day_entered        => $cols[2],
    year_entered       => $cols[3],
    date_entered       => "$cols[2]-$cols[1]-$cols[3]",   # "Day-Month-Year"
    cytogenic_location => $cols[5],
    gene_symbol        => $cols[6],
    # add more labels for the rest of the columns
  );

  # put a *reference* to this HASH onto our growling array of entries
  push @genemap_entries, \%genemap_entry;
}

It’s really important to add useful comments into your code. Not just what you are doing, but why you are doing it. In a few months time, you won’t remember any of this and if you don’t put these comments in, you’ll need to figure it out all over again.

Step 5. Process the data

Usually we would want to do something interesting with the data – such as filter out certain rows, sort these entries, etc. This would be a good place to do it, but we’ll save that for a different day.

Step 6. Output the data

We’re going to check that everything has done okay by simply printing out the entries that we’ve parsed from the file. Again, the code has lots of comments so I won’t go through it line by line.

# note: the following section is going to print out the following:
#
#   1.1    13-5-13          CTRCT8, CCV
#   1.2    25-9-01      ENO1, PPH, MPB1
#   1.3   22-12-87          ERPL1, HLM2
#   ...        ...                  ...
# 24.51    25-8-98        GCY, TSY, STA
# 24.52    20-3-08                DFNY1
# 24.53     8-2-01                  RPY
#
# Number of Genemap Entries: 15037
#

# go through these entries one by one...
foreach my $gm_entry ( @genemap_entries ) {
# we can use the keys that we defined when creating the HASH
# to access the values for each entry in a meaningful way
# note: $gm_entry is a HASH *reference*
#       to access the data in the HASH: $gm_entry->
printf "%5s %10s %20s\n", $gm_entry->{ id }, $gm_entry->{ date_entered }, $gm_entry->{ cytogenic_location };
}

print "\n"; # new line
print "Number of Genemap Entries: ", scalar( @genemap_entries ), "\n";
print "\n";

All done.

Here’s the listing of the program in full:

 

#!/usr/bin/env perl

use strict;
use warnings;

use File::Basename qw/ basename /;

# 'basename' is imported from File::Basename
my $PROGNAME = basename( $0 );

my $USAGE =<<"_USAGE";
usage: $PROGNAME <genemap_file>

Parses OMIM "genemap" file

_USAGE

my $genemap_filename = shift @ARGV or die "$USAGE";

# create a file handle that we can use to input the contents of
# the genemap file
# (and complain if there's a problem)
# note: '<' means "input from this file" in linux shells

open( my $genemap_fh, '<', $genemap_filename )
or die "! Error: failed to open file $genemap_filename: $!";

# create an array that will contain our genemap entries
my @genemap_entries;

# go through the file line by line
while( my $line = $genemap_fh->getline ) {

  # an example line from file 'genemap' looks like:
  # 1.1|5|13|13|1pter-p36.13|CTRCT8, CCV|P|Cataract, congenital, Volkmann type||115665|Fd|linked to Rh in Scottish family||Cataract 8, multiple types (2)| | ||

  # the keys for each column are specified in 'genemap.key':
  # 1  - Numbering system, in the format  Chromosome.Map_Entry_Number
  # 2  - Month entered
  # 3  - Day     "
  # 4  - Year    "
  # 5  - Cytogenetic location
  # 6  - Gene Symbol(s)
  # 7  - Gene Status (see below for codes)
  # 8  - Title
  # 9  - Title, cont.
  # 10 - MIM Number
  # 11 - Method (see below for codes)
  # 12 - Comments
  # 13 - Comments, cont.
  # 14 - Disorders (each disorder is followed by its MIM number, if
  #      different from that of the locus, and phenotype mapping method (see
  #      below).  Allelic disorders are separated by a semi-colon.
  # 15 - Disorders, cont.
  # 16 - Disorders, cont.
  # 17 - Mouse correlate
  # 18 - Reference

  # split up the line based on the '|' character
  # note: we use '\|' since writing '|' on its own has a special meaning
  my @cols = split /\|/, $line;

  # create a HASH / associative array to provide labels for these values
  # note: arrays start from '0' so we take one away from the columns mentioned above
  my %genemap_entry = (
    id                 => $cols[0],
    month_entered      => $cols[1],
    day_entered        => $cols[2],
    year_entered       => $cols[3],
    date_entered       => "$cols[2]-$cols[1]-$cols[3]",   # "Day-Month-Year"
    cytogenic_location => $cols[5],
    gene_symbol        => $cols[6],
    # add more labels for the rest of the columns
  );

  # put a *reference* to this HASH onto our growling array of entries
  push @genemap_entries, \%genemap_entry;
}

# note: the following section is going to print out the following:
#
#   1.1    13-5-13          CTRCT8, CCV
#   1.2    25-9-01      ENO1, PPH, MPB1
#   1.3   22-12-87          ERPL1, HLM2
#   ...        ...                  ...
# 24.51    25-8-98        GCY, TSY, STA
# 24.52    20-3-08                DFNY1
# 24.53     8-2-01                  RPY
#
# Number of Genemap Entries: 15037
#

# go through these entries one by one...
foreach my $gm_entry ( @genemap_entries ) {
# we can use the keys that we defined when creating the HASH
# to access the values for each entry in a meaningful way
# note: $gm_entry is a HASH *reference*
#       to access the data in the HASH: $gm_entry->
printf "%5s %10s %20s\n", $gm_entry->{ id }, $gm_entry->{ date_entered }, $gm_entry->{ cytogenic_location };
}

# let people know how many entries we've processed
print "\n"; # new line
print "Number of Genemap Entries: ", scalar( @genemap_entries ), "\n";
print "\n";