Databases

As I’ve been working to get some results done for my Ph.D. thesis, I’ve stumbled across the problem of having different data obtained through different software. Even if it’s just a matter of text files, the fields are all different and even if dealing with the same data, trying to infer relationships is a pain.
Therefore I decided to create a small database to host the data of my work and query it accordingly. I didn’t want to run a database server, so I settled for SQLite, a lightweight file-driven database, I don’t handle enormous amount of data so it should be ok. Up to now I’ve inserted parts of the Entrez Gene database. First of all I downloaded the gene_info.gz from NCBI’s FTP, which contains data such as gene name, gene symbol, and so on. Then it was a matter of filtering out non-human entries, and to do so I wrote a small script called taxon_filter.py:

[code lang=”python”]
#!/usr/bin/env python

import gzip
import sys
import csv

”"”Filters NCBI annotation files by human taxon (9606). Works directly from the source
gzipped file and outputs a tab-delimited file.”””

class ncbi:
delimiter = ‘\t’
quotechar = ‘”’
escapechar = None
doublequote = True
skipinitialspace = False
lineterminator = ‘\n’
quoting = csv.QUOTE_NONE

def init(self):
# Dialect registration
csv.register_dialect(“ncbi”, self)

ncbi()

if len(sys.argv) < 3:
print “Not enough command line arguments”
sys.exit(0)

try:
compressed_file = gzip.open(sys.argv[1])
except IOError:
print “Could not open file!”
sys.exit(-1)

delim = csv.reader(compressed_file,dialect=”ncbi”)

try:
destination_file = open(sys.argv[2],”wb”)
except IOError:
print “Can’t open destination file!”
sys.exit(-1)

write_delim = csv.writer(destination_file,dialect=”ncbi”)

write_delim.writerow(delim.next())

for row in delim:
if row[0] == “9606”:
write_delim.writerow(row)

print “Complete!”
compressed_file.close()
destination_file.close()
sys.exit(0)

[/code]

(apologies, tabs are messed up)

That filtered the file for entries belonging to taxon 9606 (_Homo sapiens) . _Then I had to keep only the interesting bits in the file, so I cut the leading comment and selected only the correct fields:

[code lang=”bash”]
sed ‘1d’ gene_info_human | cut -f2,9,3,7-8 > entrez_gene.txt
[/code]

SQLite has Python bindings (officially part of Python since 2.5) but those don’t allow the direct import of text files, so I fired up the command line sqlite3 command and created the relevant table, called entrez_gene, and imported the data:

[code lang=”bash”]
.separator “\t”
.import datafiles/File_NCBI/entrez_gene.txt entrez_gene
[/code]

Done! This is the first step, then I’ll work on creating tables for my own data.

Dialogue & Discussion