A202/A598 Introduction to Programming II (4/3 cr.)

Spring Semester 2007

Lab Notes Ten: Database related operations for class demo.

Start your MySQL server (or make sure it works), then start creating the tables, add data to them:

bash-3.00$ cat mysql_client
mysql --socket=/nobackup/dgerman/mysql/mysql.sock \
  --port=14518 --host=silo.cs.indiana.edu -u dgerman -p

bash-3.00$ ps -ef | grep dgerman
dgerman  24217     1  0 Mar09 ?        00:00:01 /u/dgerman/apache/bin/httpd -k graceful
dgerman  27589 24217  0 06:00 ?        00:00:00 /u/dgerman/apache/bin/httpd -k graceful
dgerman  27590 24217  0 06:00 ?        00:00:00 /u/dgerman/apache/bin/httpd -k graceful
dgerman  27591 24217  0 06:00 ?        00:00:00 /u/dgerman/apache/bin/httpd -k graceful
dgerman  27592 24217  0 06:00 ?        00:00:00 /u/dgerman/apache/bin/httpd -k graceful
dgerman  27593 24217  0 06:00 ?        00:00:00 /u/dgerman/apache/bin/httpd -k graceful
dgerman  29563 24217  0 08:04 ?        00:00:00 /u/dgerman/apache/bin/httpd -k graceful
root      1108  3528  0 09:51 ?        00:00:00 sshd: dgerman [priv]
dgerman   1113  1108  0 09:51 ?        00:00:00 sshd: dgerman@pts/19
dgerman   1114  1113  0 09:51 pts/19   00:00:00 -csh
dgerman   1141  1114  0 09:51 pts/19   00:00:00 bash
dgerman   1485  1141  0 09:59 pts/19   00:00:00 ps -ef
dgerman   1486  1141  0 09:59 pts/19   00:00:00 grep dgerman
bash-3.00$ pwd
/u/dgerman/bin
bash-3.00$ ./mysql_start
bash-3.00$ Starting mysqld daemon with databases from /nobackup/dgerman/mysql

bash-3.00$ ./mysql_client
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use demoOne
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------------+
| Tables_in_demoOne          |
+----------------------------+
| dgerman_1017               |
| dgerman_HW3_players        |
| dgerman_accumulator        |
| dgerman_guessTheNumber     |
| dgerman_person             |
| dgerman_samplemidterm      |
| dgerman_transactions_draft |
| dgerman_users_draft        |
| example_1024               |
| friday_players             |
| players                    |
| skfhsdf9                   |
| students                   |
+----------------------------+
13 rows in set (0.00 sec)

mysql> create table pp_users (username char(8) primary key, password varchar(40));
Query OK, 0 rows affected (0.13 sec)

mysql> describe pp_users;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | char(8)     | NO   | PRI |         |       |
| password | varchar(40) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> select * from pp_users;
Empty set (0.00 sec)

mysql> insert into pp_users values ('lbird', 'dribl'), ('dgerman', 'n0thing');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from pp_users;
+----------+----------+
| username | password |
+----------+----------+
| lbird    | dribl    |
| dgerman  | n0thing  |
+----------+----------+
2 rows in set (0.00 sec)

mysql>

Here's the second table we need (also describing an entity):

mysql> create table pp_classes (class char(4) primary key, descr varchar(80));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into pp_classes values ('a202', 'Intro to Programming II'), 
('a348', 'Mastering the WWW'), ('a597', 'Intro to Programming I (Grad)'), 
('a201', 'Intro to Programming I'), ('a598', 'Intro to Programming II (Grad)'), 
('a548', 'Mastering the WWW (Grad)');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> describe pp_classes;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| class | char(4)     | NO   | PRI |         |       |
| descr | varchar(80) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from pp_classes;
+-------+--------------------------------+
| class | descr                          |
+-------+--------------------------------+
| a202  | Intro to Programming II        |
| a348  | Mastering the WWW              |
| a597  | Intro to Programming I (Grad)  |
| a201  | Intro to Programming I         |
| a598  | Intro to Programming II (Grad) |
| a548  | Mastering the WWW (Grad)       |
+-------+--------------------------------+
6 rows in set (0.00 sec)

mysql>

Finally, here are the queries for registering and summarizing the registration.

mysql> select * from pp_users;
+----------+----------+
| username | password |
+----------+----------+
| lbird    | dribl    |
| dgerman  | n0thing  |
+----------+----------+
2 rows in set (0.00 sec)

mysql> select * from pp_classes;
+-------+--------------------------------+
| class | descr                          |
+-------+--------------------------------+
| a202  | Intro to Programming II        |
| a348  | Mastering the WWW              |
| a597  | Intro to Programming I (Grad)  |
| a201  | Intro to Programming I         |
| a598  | Intro to Programming II (Grad) |
| a548  | Mastering the WWW (Grad)       |
+-------+--------------------------------+
6 rows in set (0.00 sec)

mysql> select * from pp_registr;
Empty set (0.00 sec)

mysql> insert into pp_registr values ('dgerman', 'a348'), ('dgerman', 'a201'), ('lbird', 'a201'), ('lbird', 'a348'), ('lbird', 'a202');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from pp_registr;
+----------+-------+
| username | class |
+----------+-------+
| dgerman  | a201  |
| dgerman  | a348  |
| lbird    | a201  |
| lbird    | a202  |
| lbird    | a348  |
+----------+-------+
5 rows in set (0.00 sec)

mysql> select class, count(*) from pp_registr group by class;
+-------+----------+
| class | count(*) |
+-------+----------+
| a201  |        2 |
| a202  |        1 |
| a348  |        2 |
+-------+----------+
3 rows in set (0.01 sec)

mysql>

Everything else you need is on What's New? for this week.

Here's also my script that prints the summary of registration:

#!/usr/bin/python

import MySQLdb

# Create a connection object and create a cursor
Con = MySQLdb.Connect(host="silo.cs.indiana.edu", port=14518, user="dgerman", passwd="sp00n", db="demoOne")
Cursor = Con.cursor()

# Make SQL string and execute it
sql = " select class, count(*) from pp_registr group by class;"
Cursor.execute(sql)

# Fetch all results from the cursor into a sequence and close the connection
Results = Cursor.fetchall()

print "Content-type: text/html\n\n<html>"

print "<pre>", Results, "</pre>", "<hr>"

print "<table border cellpadding=2><tr><th>Class Name<th>Enrollment"
for row in Results:
  print "<tr><td align=center>", row[0], "<td align=center>", row[1]
print "</table>"

Con.close()

Updated by Adrian German for A202/A598