Python Programming for Biology: Bioinformatics and Beyond

Creating a table

In  this  section  we  will  work  with  a  very  simple  database,  which  we  will  call  PersonDb,

which will have just one table, called Person. The code in the following section is in the

SQL  language  not  Python,  though  later  we  go  on  to  show  how  Python  can  be  used  to

interact  via  SQL.  To  create  a  table,  we  use  the  SQL  CREATE  command.  The  simplest

version is where just the column names and types are specified. For example, suppose that

in  the  Person  table  there  are  three  columns,  firstName,  lastName  (both  strings)  and

birthYear (an integer). We could create the table via:

CREATE TABLE Person (

firstName TEXT,

lastName TEXT,

birthYear INT

);

The  SQL  keywords  are  case-insensitive  although  they  are  often  written  in  all  capital

letters. The table and column names might or might not be case-sensitive dependent on the

exact  SQL  implementation  being  used,  but  it  is  probably  safest  to  assume  that  they  are

also  case-insensitive.  The  SQL  type  for  text  strings  is  tricky.  In  most  SQL

---

implementations  there  are  several  alternatives.  TEXT  is  usually  one  of  the  alternatives,

and  it  means  that  the  strings  are  of  unlimited  length.  If  we  had  put  TEXT(30)  (for

example) it would mean that the strings are of length no more than 30. And an alternative

string  type  to  TEXT  would  be  VARCHAR,  which  is  somewhat  less  understandable,  but

has been around longer in SQL implementations.

If  a  given  column  is  mandatory,  whereby  every  record  has  to  have  a  value  for  that

column,  then  the  NOT  NULL  descriptor  is  used.  For  example,  if  the  firstName  and

lastName were mandatory in the Person table then we would have:

CREATE TABLE Person (

firstName TEXT(30) NOT NULL,

lastName TEXT(30) NOT NULL,

birthYear INT

);

Here we have also changed the strings to have a maximum length of 30.

A primary key for a table is a list of one or more columns for which the corresponding

values uniquely identify a record, and we can specify which column or columns these are.

For example, perhaps we think that the firstName and lastName uniquely identify a person

in  our  database  (although  that  is  probably  not  a  very  good  assumption).  Then  we  would

have:

CREATE TABLE Person (

firstName VARCHAR(30) NOT NULL,

lastName VARCHAR(30) NOT NULL,

birthYear INT,

PRIMARY KEY (firstName, lastName)

);

It  is  this  final  version  that  we  will  use  here.  We  have  changed  the  string  type  from

TEXT to VARCHAR  because  MySQL  does  not  allow  the  former  for  any  column  that  is

part of the primary key. With SQLite we could have stayed with TEXT(30) or indeed just

TEXT. It is these little annoyances that make life difficult if one tries to support more than

one SQL implementation.