drop database if exists usa;
create database usa;
use usa;
CREATE TABLE 'states' (
'statename' VARCHAR( 20 ) NOT NULL ,
'abbrev' VARCHAR( 10 ) NOT NULL ,
'postal' CHAR( 2 ) NOT NULL ,
'capital' VARCHAR( 20 ) NOT NULL ,
PRIMARY KEY ( 'postal' )
) TYPE=InnoDB CHARACTER SET utf8;
LOAD DATA LOCAL INFILE 'newstates.txt'
INTO TABLE 'states'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';
How might we use this database?
Suppose you would like to quiz yourself regarding capital cities. You
would like to have a web page which asks you about a state or territory
and asks you to pick from a randomly ordered list of possibilities. After
you make a selection, you click on a button, and the computer tells you
if you got it right, plus lets you go again. This is a very simple use
of a database, but it gives us some idea of how they're used.
|
|
Since the database we're using is MySQL, we need a programming language
that will talk to MySQL, meaning it's able to select and/or update its
databases. In this case, we only need to read from the states
table. There's no need to update it.
The programming language we'll use is Python. Python lets us write
scripts that generate web pages for the user or browser, and that know
how to consult MySQL. The combination of these two abilities will make
our quiz idea a reality.
How is a web page created? A web page comes to your browser through
the Internet or through some local intranet. The format it comes in
is called HTML or XHTML (these formats are slightly different) and consists
mostly of text marked up with a lot of words in pointy brackets known
as tags. HTML stands for "Hypertext Markup Language" and the
"http://" you see at the start of most URLs stands for "hypertext
protocol." The idea behind hypertext is that any word may become
a link to some other page. This is a chief characteristic of the web:
its high degree of interconnectivity.
|  |
Our Python script will have the job of providing these
tags, as they instruct the browser what to display. The tags also set
up an HTML form with a drop-down select box and clickable button.
When you buy an airline or theater ticket, or transfer money from one
bank account to another, you may well find yourself doing this by means
of HTML forms. Some script probably put it on your screen, and when
you hit a button, a script will process your transaction, probably by
talking to databases. The script might be written in Python or some
other computer language.
As you use this simple quiz program
to help you learn the capitals, think about how you're using a database,
and how your browser is interpreting tags sent to you from Python scripts.
Your teacher may want to give you more details about how the Python scripts
get the job done. You may get some idea by simply reading them. As a classroom
activity, these scripts may be available for editing. Perhaps your teacher
will show you how to get MySQL running on your own computer, or maybe
it's already running on the classroom server.
Comments:
- newstates.txt was saved in UTF-8 format from Notepad in Windows XP.
The data was originally harvested from web pages and used to populate
a spreadsheet
- different operating systems have different ways of terminating a line
of text. On Windows, the line ending is typically \r\n (carriage return,
newline), whereas on Unix or GNU (Linux), the line ending is just \n.
- Python provides a simple server for testing cgi scripts in the form
of the CGIHTTPServer module. This lets you do some testing without booting
a full-fledged server (such as Apache) on a development computer
For further reading:
|
