Python Programming for Biology: Bioinformatics and Beyond

Running SQL

We  can  run  the  SQL  program  at  the  operating-system  prompt  (‘>’)  and  then  type  the

command (or, more generally, commands) at the ‘SQL prompt’. For example, in SQLite a

session might take the form:

> sqlite3 PersonDb

… (information printed out by the program)

sqlite> CREATE TABLE Person (

…> firstName VARCHAR(30) NOT NULL,

…> lastName VARCHAR(30) NOT NULL,

…> birthYear INT,

…> PRIMARY KEY (firstName, lastName)

…> );

sqlite> .quit

---

The last command (‘.quit’ for SQLite, ‘quit’ for MySQL) exits us from the SQL prompt

back to the operating-system prompt. With MySQL it would be the same except we would

probably need to specify a username and enter a password:

> mysql –u USERNAME –p PersonDb

Enter password:

… (information printed out by the program)

mysql> CREATE TABLE Person (

-> firstName VARCHAR(30) NOT NULL,

-> lastName VARCHAR(30) NOT NULL,

-> birthYear INT,

-> PRIMARY KEY (firstName, lastName)

-> );

mysql> quit

Here USERNAME should be replaced with the appropriate value. And the ‘-p’ flag tells

the  mysql  program  to  ask  for  a  password  before  SQL  commands  can  be  entered.

Alternatively,  and  this  is  the  normal  practice,  we  could  put  the  command  (or  more

generally,  commands)  into  a  file  and  run  this.  So  suppose  we  place  this  CREATE

command  in  a  file  called  createPersonTable.sql,  then  to  create  the  table  in  SQLite  we

would run:

> sqlite3 PersonDb < createPersonTable.sql

This just runs the script. It does not then leave you at the SQL prompt, but instead back

at the operating-system prompt. With MySQL it would be the same except for the issue of

the username and password:

> mysql –u USERNAME –p PersonDb < createPersonTable.sql