Python Programming for Biology: Bioinformatics and Beyond

Figure 20.1.  SQL database showing two tables. The table header shows what data is

stored in the table, and each row represents one record of data. Here the second table,

Chain, has a link, called a foreign key, to the first table, Structure. Thus, for each row in

the Chain table there is a unique row in the Structure table (but not vice versa, in general).

The table creation in MySQL is the same except that we need to explicitly specify the

automatic increment feature of the primary keys in each of the four tables:

id INTEGER AUTO_INCREMENT,

We use ‘CREATE TABLE’ followed by the table name, to create that table, and then we

list  the  columns  that  exist  in  the  table,  with  their  properties.  The  ‘NOT  NULL’  property

means  that  the  corresponding  attribute  is  mandatory.  Here,  most  of  our  attributes  are

mandatory.  The  PRIMARY  KEY  indicates  the  column  or  columns  that  make  up  the

primary  key.  And  the  FOREIGN  KEY  indicates  that  the  relevant  column  refers  to  a

column (normally a key) in another table. In particular, on insertion of any record in this

table, the SQL implementation should check that there is a record in the other table with a

value in the other column equal to that for the column in this table. In essence it’s about

consistency of information between tables.

If we place these SQL commands in a file called createStructureTables.sql and assume

---

that  the  database  is  called  StructureDb,  we  can  then  create  the  tables  by  issuing  the

following operating-system command for SQLite (at the command line prompt):

> sqlite3 StructureDb < createStructureTables.sql

We could also create the equivalent script that deletes  (or  ‘drops’)  the  tables,  and  that

would be accomplished by the much simpler SQL script:

DROP TABLE structure;

DROP TABLE chain;

DROP TABLE residue;

DROP TABLE atom;

If we place that in the file dropStructureTables.sql, then we can delete the tables via:

> sqlite3 StructureDb < dropStructureTables.sql

Obviously one has to be very careful about running such a script. It is good for testing

purposes, but in real life it would be unusual to want to delete an entire set of tables like

this. The MySQL versions of dropping tables would be the same except for the possible

requirement of a username and password.

> mysql –u USERNAME –p StructureDb < createStructureTables.sql

> mysql –u USERNAME –p StructureDb < dropStructureTables.sql

You  could  also  write  SQL  scripts  to  insert,  modify,  query  and  delete  records,  but  we

will  do  this  using  Python.  If  instead  of  having  one  table  per  class  we  decided  to  instead

have  just  one  table  containing  all  the  information,  much  of  it  redundant,  then  we  could

have the following creation command (in SQLite):

CREATE TABLE structure (

id INTEGER,

structureName TEXT NOT NULL,

structurePdbId TEXT NOT NULL,

structureConformation INTEGER NOT NULL,

chainMolType TEXT NOT NULL,

chainCode TEXT NOT NULL,

residueSeqId INTEGER NOT NULL,

residueCode TEXT,

atomName TEXT NOT NULL,

atomX FLOAT NOT NULL,

atomY FLOAT NOT NULL,

atomZ FLOAT NOT NULL,

atomElement TEXT NOT NULL,

PRIMARY KEY (id)

);

In  MySQL  we  would  add  AUTO_INCREMENT  to  the  id.  Note  that  the  ‘ATOM’

records in a PDB file could be thought of as representing this kind of table, and indeed in a

PDB file there is much redundant information, e.g. the residue code is repeated over and

over for each atom in the same residue. As noted previously, this kind of universal table

can be desirable if the database is mainly for querying.

---