Python Programming for Biology: Bioinformatics and Beyond

Manipulating records

To  add  records  to  the  database  we  use  the  INSERT  command.  Here  we  must  specify

values  for  the  columns  that  are  mandatory  (i.e.  NOT  NULL),  but  naturally  can  also

specify  the  values  for  other  columns.  For  example,  the  following  commands  insert  three

records into the Person table:

INSERT INTO Person (firstName, lastName) VALUES ('Mary', 'Jones');

INSERT INTO Person (firstName, lastName) VALUES ('Tom', 'Smith ');

INSERT INTO Person (firstName, lastName, birthYear) VALUES ('Susan',

'Brown',

1723);

We can modify the data in existing records, subject to the various constraints, such as

data type and uniqueness of the primary key, still being satisfied:

UPDATE Person SET birthYear=1942 WHERE lastName= 'Smith';

UPDATE Person SET firstName= 'Ann', birthYear=2001 WHERE lastName= 'Jones';

We  can  query  the  database  and  determine  which  records  satisfy  specified  constraint

criteria (here born after 1900):

SELECT lastName FROM Person WHERE birthYear > 1900;

---

To remove records from the database we use the DELETE command. We can specify

conditions  for  deletion:  for  example,  the  following  only  deletes  records  where  the

firstName is ‘Mary’:

DELETE FROM Person WHERE firstName= 'Mary';

And the following deletes records where the lastName is ‘Brown’ and the birthYear  is

1723:

DELETE FROM Person WHERE lastName= 'Brown' AND birthYear=1723;

We can delete all the records in one go from a table by omitting the WHERE clause:

DELETE FROM Person;

This does not remove the table, just all the records inside the table. To remove an entire

table from the database we use the DROP command:

DROP TABLE Person;

These last two commands are rather dramatic in their effect and obviously should only

be carried out with due care. SQL has much more complexity than we have shown in this

section, but this is enough to get started. This is a book about Python after all.