Spring Semester 2007


Lab Five: Populating your database with data (and accessing it) using Perl.

1. Here's how you can copy tables from someone else (if the tables exist and are good):

Script started on Tue Aug 03 09:10:06 2004
burrowww.cs.indiana.edu% mysql -ua348 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11268 to server version: 4.0.18-standard

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

mysql> use a348
Database changed
mysql> show tables like '%ninliu%HW%';
+------------------------------+
| Tables_in_a348 (%ninliu%HW%) |
+------------------------------+
| ninliu_HW3_enrollment        |
| ninliu_HW3_matches           |
| ninliu_HW3_participation     |
| ninliu_HW3_players           |
| ninliu_HW3_spectators        |
| ninliu_HW3_venues            |
+------------------------------+
6 rows in set (0.04 sec)

mysql> show tables like 'lbird%';
+-------------------------+
| Tables_in_a348 (lbird%) |
+-------------------------+
| lbird_board             |
| lbird_message           |
| lbird_newtable          |
| lbird_person            |
+-------------------------+
4 rows in set (0.04 sec)

mysql> describe ninliu_HW3_enrollment;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| matchID      | varchar(30)  | YES  |     | NULL    |       |
| tickets      | decimal(4,0) | YES  |     | NULL    |       |
| universityID | varchar(25)  | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> create table lbird_HW3_enrollment select * from ninliu_HW3_enrollment;
Query OK, 22 rows affected (0.04 sec)
Records: 22  Duplicates: 0  Warnings: 0

mysql> show tables like 'lbird%';
+-------------------------+
| Tables_in_a348 (lbird%) |
+-------------------------+
| lbird_HW3_enrollment    |
| lbird_board             |
| lbird_message           |
| lbird_newtable          |
| lbird_person            |
+-------------------------+
5 rows in set (0.05 sec)

mysql> describe ninliu_HW3_enrollment; 
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| matchID      | varchar(30)  | YES  |     | NULL    |       |
| tickets      | decimal(4,0) | YES  |     | NULL    |       |
| universityID | varchar(25)  | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table lbird add key (matchID, universityID);
Query OK, 22 rows affected (0.07 sec)
Records: 22  Duplicates: 0  Warnings: 0

mysql> describe lbird_HW3_enrollment;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| matchID      | varchar(30)  | YES  | MUL | NULL    |       |
| tickets      | decimal(4,0) | YES  |     | NULL    |       |
| universityID | varchar(25)  | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table lbird_HW3_enrollment add primary key (matchID, universityID); 
Query OK, 22 rows affected (0.07 sec)
Records: 22  Duplicates: 0  Warnings: 0

mysql> describe lbird_HW3_enrollment;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| matchID      | varchar(30)  |      | PRI |         |       |
| tickets      | decimal(4,0) | YES  |     | NULL    |       |
| universityID | varchar(25)  |      | PRI |         |       |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from lbird_HW3_enrollment;
+---------+---------+----------------+
| matchID | tickets | universityID   |
+---------+---------+----------------+
| 1       |     120 | Illinois       |
| 1       |     100 | IU Bloomington |
| 2       |     140 | Illinois       |
| 2       |      60 | Purdue         |
| 3       |      20 | U of Minnesota |
| 3       |     220 | Michigan       |
| 4       |     300 | Michigan       |
| 4       |      80 | U of Minnesota |
| 5       |     180 | Iowa           |
| 6       |     120 | Purdue         |
| 6       |     210 | IU Bloomington |
| 7       |     150 | IU Bloomington |
| 7       |     200 | Iowa           |
| 8       |     180 | Illinois       |
| 9       |     120 | U of Minnesota |
| 9       |      40 | Illinois       |
| 10      |     200 | Purdue         |
| 11      |     120 | Iowa           |
| 11      |      80 | Purdue         |
| 12      |      60 | IU Bloomington |
| 12      |      60 | U of Minnesota |
| 12      |     240 | Michigan       |
+---------+---------+----------------+
22 rows in set (0.00 sec)

mysql> exit
Bye
burrowww.cs.indiana.edu% exit
burrowww.cs.indiana.edu% 
script done on Tue Aug 03 09:14:11 2004
2. Second, here are two examples of scripts that extract info from the database.

The first script is available here, the code is listed below.

#!/usr/bin/perl

use DBI; 
use CGI; 

$q = new CGI; 

print "Content-type: text/html\n\n"; 

$DB        = "DBI:mysql:a348";      # data source name (database)
$username  = "a348";                # username
$password  = "a348AG";              # password

$dbh = DBI->connect($DB, $username, $password, {PrintError => 0})
  || die "Couldn't open database: ", $DBI::errstr; 

$query = "select * from dgerman_HW3_players"; 

$sth = $dbh->prepare($query) || die $dbh->errstr;

$sth->execute() || die $sth->errstr;

print qq{
  <table border cellpadding=2>
    <tr><th> Username 
        <th> First Name 
        <th> Last Name 
        <th> Picture  
        <th> Address 
        <th> City  
        <th> State 
        <th> Player rate per match 

}; 

while (my $row = $sth->fetch) {
  my(@values) = @$row; 
  print "<tr>"; 
  foreach $value (@values) {
    print "<td>$value"; 
  } 

} 

print "</table>"; 

$sth->finish; 
The second script is available here, the code is listed below.

#!/usr/bin/perl

use DBI; 
use CGI; 

$q = new CGI; 

print "Content-type: text/html\n\n"; 

$DB        = "DBI:mysql:a348";      # data source name (database)
$username  = "a348";                # username
$password  = "a348AG";              # password

$dbh = DBI->connect($DB, $username, $password, {PrintError => 0})
  || die "Couldn't open database: ", $DBI::errstr; 

$query = "select * from dgerman_HW3_players"; 

$sth = $dbh->prepare($query) || die $dbh->errstr;

$sth->execute() || die $sth->errstr;

print qq{
  <table border cellpadding=2>
    <tr>

        <th> First Name 
        <th> Last Name 
         
        <th> City  
         
        <th> Player rate per match 

}; 

while (my $row = $sth->fetch) {
  my(@values) = @$row; 
  print "<tr>"; 
  foreach $i (1, 2, 5, 7) {
    print "<td>", $values[$i]; 
  } 

} 

print "</table>"; 

$sth->finish; 
3. Finally, here's a script to create tables and populate them with data:
#!/usr/bin/perl

use DBI; 

$DB        = "DBI:mysql:a348";      # data source name (database)
$username  = "a348";                # username
$password  = "a348AG";              # password

$dbh = DBI->connect($DB, 
                    $username, 
                    $password, {PrintError => 0}) || 
       die "Couldn't open database: ", $DBI::errstr; 

print "I have opened the database...\n"; 

$query = qq{ 
  create table dgerman_HW3_players (
    username   varchar(8) primary key, 
    firstName  varchar(20), 
    lastName   varchar(20), 
    picture    varchar(120), 
    address    varchar(120), 
    city       varchar(30), 
    state      varchar(2), 
    playerRatePerMatch 
               decimal(12, 2)
  )
};   

print " Getting ready to create dgerman_players... "; 

$dbh->do($query) || die $dbh->errstr;

print "done.\n Getting ready to populate tables with data. \n"; 

open (INPUT, "players_data"); 
$line = <INPUT>; 
@names = split(/,/, $line); 
while ($line = <INPUT>) {
  ($uname, $first, $last, $pic, $addr, $city, $st, $rate) = 
                                             split(/,/, $line, 8); 
  $st =~ s/\s//g; 
  chop($rate); 
  $query = qq{
    insert into dgerman_HW3_players values 
      ('$uname', 
       '$first', 
       '$last', 
       '$pic', 
       '$addr', 
       '$city', 
       '$st', 
       $rate 
      )
  }; 

  # print $query, "\n"; 

  $dbh->do($query) || die $dbh->errstr; 

}
close(INPUT); 
Here it is in action, together with the data it needs:
burrowww.cs.indiana.edu% ls -l
total 6
-rw-r--r--   1 dgerman  faculty       704 Feb 19 11:50 players_data
-rwx------   1 dgerman  faculty      2438 Feb 19 11:50 script
burrowww.cs.indiana.edu% ./script
I have opened the database...
 Getting ready to create dgerman_players... done.
 Getting ready to populate tables with data. 
burrowww.cs.indiana.edu% cat players_data
username, firstName, lastName, picture , address             , city        , state, playerRatePerMatch 
cbarkley, Charles  , Barkley , some URL, Sir Charles Avenue  , Phoenix     , AZ   ,         450.00 
lbird   , Larry    , Bird    , some URL, Freedom Lick Rd.    , Naples      , FL   ,         350.00 
mjordan , Michael  , Jordan  , some URL, Jordan Air Way      , Chicago     , IL   ,         500.00 
rmiller , Reggie   , Miller  , some URL, Miller Time Road    , Indianapolis, IN   ,         150.00 
tduncan , Tim      , Duncan  , some URL, Duncan at the Tim   , Houston     , TX   ,         210.00 
tkukoc  , Toni     , Kukoc   , some URL, Petar Zrinski Street, New Zagreb  , PA   ,          50.00 
burrowww.cs.indiana.edu% 
Here's what the database looks like now (and two queries):
mysql> describe dgerman_HW3_players;
+--------------------+---------------+------+-----+---------+-------+---------------------------------+
| Field              | Type          | Null | Key | Default | Extra | Privileges                      |
+--------------------+---------------+------+-----+---------+-------+---------------------------------+
| username           | varchar(8)    |      | PRI |         |       | select,insert,update,references |
| firstName          | varchar(20)   | YES  |     | NULL    |       | select,insert,update,references |
| lastName           | varchar(20)   | YES  |     | NULL    |       | select,insert,update,references |
| picture            | varchar(120)  | YES  |     | NULL    |       | select,insert,update,references |
| address            | varchar(120)  | YES  |     | NULL    |       | select,insert,update,references |
| city               | varchar(30)   | YES  |     | NULL    |       | select,insert,update,references |
| state              | char(2)       | YES  |     | NULL    |       | select,insert,update,references |
| playerRatePerMatch | decimal(12,2) | YES  |     | NULL    |       | select,insert,update,references |
+--------------------+---------------+------+-----+---------+-------+---------------------------------+
8 rows in set (0.00 sec)

mysql> select firstName, lastName, city, playerRatePerMatch from dgerman_HW3_players;
+-----------+----------+---------------+--------------------+
| firstName | lastName | city          | playerRatePerMatch |
+-----------+----------+---------------+--------------------+
|  Charles  |  Barkley |  Phoenix      |             450.00 |
|  Larry    |  Bird    |  Naples       |             350.00 |
|  Michael  |  Jordan  |  Chicago      |             500.00 |
|  Reggie   |  Miller  |  Indianapolis |             150.00 |
|  Tim      |  Duncan  |  Houston      |             210.00 |
|  Toni     |  Kukoc   |  New Zagreb   |              50.00 |
+-----------+----------+---------------+--------------------+
6 rows in set (0.00 sec)

mysql> select * from dgerman_HW3_players;
+----------+-----------+----------+-----------+-----------------------+---------------+-------+--------------------+
| username | firstName | lastName | picture   | address               | city          | state | playerRatePerMatch |
+----------+-----------+----------+-----------+-----------------------+---------------+-------+--------------------+
| cbarkley |  Charles  |  Barkley |  some URL |  Sir Charles Avenue   |  Phoenix      | AZ    |             450.00 |
| lbird    |  Larry    |  Bird    |  some URL |  Freedom Lick Rd.     |  Naples       | FL    |             350.00 |
| mjordan  |  Michael  |  Jordan  |  some URL |  Jordan Air Way       |  Chicago      | IL    |             500.00 |
| rmiller  |  Reggie   |  Miller  |  some URL |  Miller Time Road     |  Indianapolis | IN    |             150.00 |
| tduncan  |  Tim      |  Duncan  |  some URL |  Duncan at the Tim    |  Houston      | TX    |             210.00 |
| tkukoc   |  Toni     |  Kukoc   |  some URL |  Petar Zrinski Street |  New Zagreb   | PA    |              50.00 |
+----------+-----------+----------+-----------+-----------------------+---------------+-------+--------------------+
6 rows in set (0.01 sec)

mysql> 

Updated by Adrian German for A348/A548