How To Create A Mysql Database And Interact With It Via Python

Question

[Olivier]

Hello

I would like to learn how to create a MySQL database for genomic sequences/ annotations and interact with it via python. Is there a comprehensive tutorial available on that? Which sequence format is best to use to build such a database? I managed to install MySQL and load the BioSQL schema, as mentionned on http://biopython.org/wiki/BioSQL , but that's all I could manage. I don't know how to go ahead.

Thanks.

[Neilfws]

If you've installed MySQL + BioSQL, you've done most of the hard work. Loading Genbank sequences into the database is relatively straightforward, as described at the link that you posted. There isn't much more documentation around than that wiki page; I think you'll just have to study it, follow any links and read the documentation of the relevant Biopython modules.

Answer 1

[Pierre Lindenbaum]

The schema used by the UCSC is simple, there is a table defined for each track. For example here is the table for the SNPs:

$ mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A -D hg19   -e 'desc snp135'  
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field           | Type                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | Null | Key | Default | Extra |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+
| bin             | smallint(5) unsigned                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | NO   |     |         |       |
| chrom           | varchar(31)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | NO   | MUL |         |       |
| chromStart      | int(10) unsigned                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | NO   |     |         |       |
| chromEnd        | int(10) unsigned                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | NO   |     |         |       |

(...)

The first column 'bin' is an index to quickly find the features in a given region. See http://genomewiki.ucsc.edu/index.php/Binindexingsystem

See also: Using MySQL With Python http://dev.mysql.com/usingmysql/python/

[Olivier]

Thanks Pierre But I'm still a beginner, and was looking for a tutorial that describes how to add a genome to a mysql database and interact with it using Biopython. I've got GenBank files of Arabidopsis chromosomes and would like to set up a its database and do some manipulations via biopython. UCSC doesn't seem to contain plant sequences..

[Pierre Lindenbaum]

then you should first have a look at the mysql tutorial : http://dev.mysql.com/doc/refman/5.5/en/tutorial.html

[Olivier]

Hello again. After upgrading to Ubuntu 12.04, I now have to use MySQL5.5 and I get this output when I tried to load the biosql schema: ~/Downloads/biosql-1.0.1/sql$ mysql -u root -p biosql < biosqldb-mysql.sql Enter password: ERROR 1064 (42000) at line 49: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=INNODB' at line 8.

I have done everything as mentioned in the BioSQL schema v1.0.1 folder, yet it doesn't work. Grateful if someone could help.

[Olivier]

I just succeeded importing the schema to MySQL 5.5 by replacing all occurrences of 'TYPE=INNODB' by 'ENGINE=INNODB' in the biosqldb-mysql.sql file. Seems to work.