Transcript Introduction to Perl Programming

Intermediate
Perl Programming
Class Three
Instructor: Byrne Reese
X401
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
Review from Last Week…
1.
2.
3.
4.
Introduction to CPAN
CGI Programming
Using Template Toolkit
Writing a “Remember Me” web app
using Cookies
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
Today’s Agenda
1.
2.
3.
4.
5.
6.
Homework Review
Web Automation
Writing a Link Checker
SQL Primer
MySQL and other free databases
DBI Module
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
Homework Review
• Add a “Remember Me” checkbox to the
Hello World form:
–
–
If the user has a cookie set that identifies
them as a returning user, then do not
prompt them for a name to say hello to.
The cookie should only be “session based.”
• Extra Credit:
–
Give the user a way of clearing the cookie
without restarting the browser.
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
Web Automation
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
LWP
• What’s in a name?
–
“LibWww-Perl”
• Automate form posting
• “Web Scraping”
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
Exercise
• Write a command line script that:
– Takes as input a single URL
– Then prints to the screen the HTML content
of that URL
• Hint: Use the “LWP” Perl Module
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
Automating a Form Post
# Create a user agent object
use LWP::UserAgent;
$ua = LWP::UserAgent->new;
# Create a request
my $req = HTTP::Request->new(
GET => 'http://localhost/cgi-bin/hello.cgi‘
);
# Pass request to the user agent and get a response back
my $res = $ua->request($req);
# Check the outcome of the response
if ($res->is_success) {
print $res->content;
} else {
print $res->status_line, "\n";
}
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
Exercise
• Write a script that automates a Google
Search:
–
–
–
Takes as input a single keyword
Executes query at google.com
Displays search results in HTML to screen
• Hints:
–
man LWP;
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
Exercise
• Write a link checker:
–
–
–
Takes as input a single URL
For each href located on that page, verify
that the link is valid.
Print out the links to validate and their
status as you go.
• Hints:
–
Regular expressions to extract URLs
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
SQL Primer
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
About SQL
• Structured Query Language
• Standard
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
Our Database
• Table 1: “users”
–
–
–
user_id (primary key)
username
password
• Table 2: “bookmarks”
–
–
–
user_id (foreign key)
link
label
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
Setup the Database
 scp [email protected]:./*.sql .
Password: “iloveperl”
 mysqladmin create –u root perlclass
 mysql –u root perlclass < schema_data.sql
 mysql –u root perlclass
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
INSERT Statement
• Adds a single row to the selected
database table
• Example:
INSERT INTO users (username, password)
VALUES (‘byrne’,’reese’);
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
UPDATE Statement
• Modifies a row or rows in the selected
database table
• Example:
UPDATE users
SET password=‘reese’
WHERE username=‘byrne’
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
DELETE Statement
• Deletes a row or rows in the selected
database table
• Example:
DELETE FROM users
WHERE username=‘byrne’
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
SELECT Statement
• Reads a row or rows in the selected
database table
• Example:
SELECT username,password
FROM users
WHERE username=‘john’
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
JOINs
• Selects and associates data located in
two different tables into a single result
set.
• Primary keys and Foreign keys
• Example:
SELECT
FROM
WHERE
AND
b.label, b.link
users u, bookmarks b
u.username=‘byrne’
u.user_id = b.user_id
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
15 Minute Break
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
Using Perl’s DBI
•
•
•
•
•
Installing DBI and DBD::mysql
Connecting to the Database
Preparing a Statement
Executing a Statement
Executing a Query and parsing the
results
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
Connecting to the Database
• Database “Handles”
• Example: connect_to_db():
use DBI;
sub connect_to_db {
my $dbh = DBI->connect("DBI:mysql:$DB:$DBHOST",
"$DBUSER","$DBPASS",{RaiseError => 1})
or die "connecting : $DBI->errstr\n";
return $dbh;
}
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
Executing a Statement
Example:
my $dbh = connect_to_db();
my $sql = "DELETE FROM users";
my $sth = $dbh->prepare($sql);
$sth->execute;
$sth->finish;
$dbh->close;
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
Executing a Query
Example:
my $dbh = connect_to_db();
my $sql = "SELECT username,password FROM users";
my $sth = $dbh->prepare($sql);
$sth->execute;
while (($username,$password) = $sth->fetchrow) {
print “username=$username, password=$password\n”;
}
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.
Exercise
• Create an address book:
–
Write subroutines capable of creating,
reading, updating and deleting (CRUD) a
single row in the database
• Extra Credit:
–
Make it command line accessible
Copyright 2007 Byrne Reese.
Distributed under Creative Commons, share and share alike with attribution.