Python Programming for Biology: Bioinformatics and Beyond

Basic SQL

There  are  many  available  pieces  of  software  that  implement  relational  databases.  Most

relational database implementations provide client software into which you can type SQL

---

commands.  The  client  usually  also  allows  SQL  scripts  to  be  run.  Many  implementations

also  have  a  separate  server,  which  receives  commands  from  the  client  and  actually

executes them, referring to the data stored in the database, and then gives back the result

to the client.

Much of the SQL syntax is well standardised, but most implementations stray slightly

from  standards  and  also  provide  extra  functionality  that  is  non-standard.  Where  such

differences matter, we will look at two relational databases in this chapter, MySQL

1

 and

SQLite.

2

 MySQL  is  the  most  common  open-source  relational  database.  SQLite  is  a

lightweight implementation that requires no conventional server to be installed or run; it is

all handled by the client and it stores the entire database in a single file on disk.

There  are  Python  wrappers  around  SQL  for  many  of  the  relational  database

implementations.  With  the  exception  of  SQLite,  these  wrappers  are  not  part  of  the

standard Python distribution, and so require a separate download. For MySQL the Python

module  is  called  MySQLdb.

3

 Note  that  for  MySQL  you  will  also  need  the  underlying

database  implementation  to  be  on  your  computer,  in  addition  to  the  Python  wrapper

around it. Many Linux/UNIX distributions will automatically have SQLite installed, but it

is less common to have MySQL installed. We will assume in this chapter that the SQLite

program (executable) is called sqlite3 and that the MySQL client program is called mysql.

The  wrappers  to  these  database  implementations  allow  Python  to  be  used  to  create  or

delete tables, and, more commonly, to insert or delete or modify records in the database, or

simply  to  query  the  database.  Unfortunately  the  interaction  methodology  is  rather  weak:

the database constructs are not directly modelled, and the way information is passed from

the  Python  world  to  the  database  world  is  just  via  textual  strings.  This  problem  is  not

unique  to  Python;  other  languages,  like  Java,  have  the  same  issue  when  communicating

with an SQL database.