Managing local biological databases with the BioSQL module. · Biopython

Python Tools for Computational Molecular Biology
BioSQL is a joint effort between the OBF projects (BioPerl, BioJava etc) to support a shared database schema for storing sequence data. In theory, you could load a GenBank file into the database with BioPerl, then using Biopython extract this from the database as a record object with features - and get more or less the same thing as if you had loaded the GenBank file directly as a SeqRecord using SeqIO.
We have some existing documentation (HTML, PDF) for the Biopython interfaces to BioSQL, covering installing Python database adaptors and basic usage of BioSQL. This is a little old, and I am hoping to use this wiki page to update the above documentation in future.
The following text applies to Biopython 1.64 or later (and assumes Python 3).
This is fairly complicated - partly because there are so many options. For example, you can use a range of different SQL database packages (we’ll focus on MySQL), you can have the database on your own computer (the assumption here) or on a separate server, and of course there are usernames and passwords associated with database. And finally the details will also vary depending on your operating system.
This text is based in part on the BioSQL schema INSTALL instructions which also covers alternatives to MySQL.
You will need to install some database software plus the associated python library so that Biopython can “talk” to the database. In this example we’ll talk about the most common choice, MySQL. How you do this will also depend on your operating system, for example on a Debian or Ubuntu Linux machine try this:
A password is required for logon. Please refer to MySQL documentation.
Python also needs a database driver to access MySQL database. In your virtual environment:
It will also be important to have perl (to run some of the setup scripts). Again, on a Debian or Ubuntu Linux machine try this:
You may find perl is already installed.
For Windows users, see BioSQL on Windows.
Once the software is installed, your next task is to setup a database and import the BioSQL schema (i.e. setup the relevant tables within the database). See BioSQL downloads – you’ll need to unzip the archive.
Alternatively to get the very latest BioSQL, check out their git repository. Or, navigate to the relevant schema file for your database and download just that, e.g. biosqldb-mysql.sql for MySQL. You will also want the NCBI Taxonomy loading perl script, load_ncbi_taxonomy.pl.
The following command line should create a new database on your own computer called bioseqdb, belonging to the root user account:
We can then tell MySQL to load the BioSQL scheme we downloaded above. Change to the scripts subdirectory from the unzipped BioSQL download, then:
You can have a quick play using the mysql command line tool, for example:
Or, to look inside a table:
This should return no rows as the table is empty.
IMPORTANT NOTE FOR POSTRESQL USERS: Before loading the biosqldb-pg.sql schema into Postgres you must delete the two RULES named rule_bioentry_i1 and rule_bioentry_i2; lines 771-791 in biosqldb-pg.sql BioSQL version 1.0.1
First you need to set up user permissions, if you are not sure how to do this, try:
Then, assuming you are logged-in as and Postgres is running on the local machine, you should be able to do the following:
Run psql and type enter *\d * to see all the entities created.
The BioSQL package includes a perl script under scripts/load_ncbi_taxonomy.pl to download and update the taxonomy tables. The script should be able to download the files it needs from the NCBI taxonomy FTP site automatically.
Prior to Biopython 1.49, if you wanted to work with the NCBI taxonomy database it was good idea to pre-load the NCBI taxonomy before you start trying to load sequences into the database. This isn’t so important with Biopython 1.49 onwards, where you can instead opt to have the information needed downloaded as needed from Entrez.
To update the NCBI taxonomy, change to the scripts subdirectory from the unzipped BioSQL download, then:
For PostgreSQL you need to have the perl DBD-Pg module installed - Using CPAN: “perl -MCPAN -e ‘install DBI’; perl -MCPAN -e ‘install DBD::Pg’”. Substitute Pg for mysql in the above command.
There is about 10MB to fetch, so it can take a little while (and doesn’t give any feedback while this happens). If you are worried, open a file browser window and check to see it is downloading a file called taxdump.tar.gz to the taxdata subdirectory.
You should see this output at the command prompt - be warned that some of these steps do take a while (especially rebuilding nested set left/right values):
This might be a good point for a tea break - I didn’t time this but it was over ten minutes.
One the initial tables have been populated, re-running the script is much faster. You can run this script again to update the taxonomy tables, which the NCBI do add to regularly. You may want to setup a scheduled job to do this automatically (say once a fortnight).
P.S. It is a particularly good idea to do update the taxonomy if you will be working with the left/right values in the taxon table (see also BioSQL enhancement request GitHub issue 14 (Redmine 2493)). Biopython 1.67 onwards will do this when writing to the taxon tables.
Note Biopython ignores these optional fields when loading or retrieving sequences - instead using just the parent link. See http://www.oreillynet.com/pub/a/network/2002/11/27/bioconf.html for more about how this alternative tree representation works.
Because there are so many ways you could have setup your BioSQL database, you have to tell the unit test a few bits of information by editing the file Tests/setup_BioSQL.py and filling in the following fields:
and a little lower down,
Change these to match your setup. You can then run the BioSQL unit tests as normal, e.g.
For PostgreSQL, use:
BioSQL lets us define named “sub” databases or “namespaces” within the single SQL database (which we called bios