Python Programming for Biology: Bioinformatics and Beyond

SQL creation of the database

Although we could use Python to create the database and the tables in the database, this is

often done directly in SQL because it is a one-off exercise (except occasional upgrades to

---

the data model) and there are no particular advantages to using Python in this context.

To create a database in SQLite we just need to refer to it when we do anything, so in

particular  when  we  create  the  tables  it  will  automatically  create  the  database  if  it  is  not

already created. In MySQL you have to create the empty database before you can use it,

and often the system administrator has to do this for you, and set up suitable privileges so

that you can access it. We will assume here that the database has been created.

Given  the  model  discussed  in  the  previous  section,  the  table  creation  in  SQLite  is  as

follows:

CREATE TABLE structure (

id INTEGER,

name TEXT NOT NULL,

pdbId TEXT NOT NULL,

conformation INTEGER NOT NULL,

PRIMARY KEY (id)

);

CREATE TABLE chain (

id INTEGER,

structureId INTEGER NOT NULL,

molType TEXT NOT NULL,

code TEXT NOT NULL,

PRIMARY KEY (id),

FOREIGN KEY (structureId) REFERENCES structure(id)

);

CREATE TABLE residue (

id INTEGER,

chainId INTEGER NOT NULL,

seqId INTEGER NOT NULL,

code TEXT,

PRIMARY KEY (id),

FOREIGN KEY (chainId) REFERENCES chain(id)

);

CREATE TABLE atom (

id INTEGER,

residueId INTEGER NOT NULL,

name TEXT NOT NULL,

x FLOAT NOT NULL, y FLOAT NOT NULL,

z FLOAT NOT NULL,

element TEXT NOT NULL,

PRIMARY KEY (id),

FOREIGN KEY (residueId) REFERENCES residue(id)

);

---