Perl and SQLite, free pdf download
You can download “Perl Scripting for SQLite”.
It’s a pdf of the slides I’ve made for the “Linux day 2011” in my hometown.
Have fun!
You can download “Perl Scripting for SQLite”.
It’s a pdf of the slides I’ve made for the “Linux day 2011” in my hometown.
Have fun!
Library FOR WWW in Perl (LWP)
In Linux you can istall all the perl modules about the web (LWP, URI, URL, HTTP…) at once:
:~$ sudo apt-get install libwww-perl
LWP is the most used Perl module for accessing data on the web.
LWP::Simple – module to get document by http
its functions don’t support cookies or authorization, setting header lines in the HTTP request; and generally, they don’t support reading header lines in the HTTP response (most notably the full HTTP error message, in case of an error). To get at all those features, you’ll have to use the LWP::UserAgent;
LWP::UserAgent is a class for “virtual browsers,” which you use for performing requests, and HTTP::Response is a class for the responses (or error messages) that you get back from those requests.
There are two objects involved: $browser, which holds an object of the class LWP::UserAgent, and then the $response object, which is of the class HTTP::Response. You really need only one browser object per program; but every time you make a request, you get back ar esponse object, which will have some interesting attributes:
–$response->is_success : A HTTP status line, indicating success or failure (like “404 Not Found”).
– $response->content_type A MIME content-type like “text/html”, “image/gif”, “application/xml”, and so on, which you can see with
– $response->content : the actual content of the response. If the response is HTML, that’s where the HTML source will be; if it’s a GIF, then $response->content will be the binary GIF data.
Enabling Cookies
A default LWP::UserAgent object acts like a browser with its cookies support turned off.
You can even activate cookies, with the following function:
$browser->cookie_jar({});
with “cookie_jar” you can get and save the cookies from Browsers.
The following script gets a url from the shell and print the content of the corresponding web page both to screen and a new file called “code.html” (created by running the script).
———————————————————-webclient.pl———————————–
#!/usr/bin/perl -w
use LWP::UserAgent;
#browser = instance of the UserAgent class
my $browser = LWP::UserAgent->new;
my $url =$ARGV[0]; # passing the url by command line
my $response = $browser->get($url);
die "Can’t get $url \n", $response->status_line
unless $response->is_success;
# check if the content is html
die "Hey, I was expecting HTML, not ", $response->content_type
unless $response->content_type eq 'text/html';
print "Page content: \n";
#print content to console
print $response->decoded_content;
#print content to a NEW file
open (MYPAGE, '>>code.html');
print MYPAGE $response->decoded_content;
close (MYPAGE);
#REGULAR EXPRESSION: search for a string in the content
if($response->content =~ m/perl/i) {
print " \n \n This page is about Perl!\n \n";
} else {print "\n \n No content about Perl! \n \n"; }
Let’s analyze the following perl scripta “cinema.pl”, to create and populate a simple database about movies, and “query_hollywood.pl” to execute a simple SELECT-FROM-WHERE query on it.
—————————————cinema.pl————————————————————
#!/usr/bin/perl -w
use DBI;
use strict;
# CONFIG VARIABLES
my $platform = “SQLite”;
my $database = “hollywood.db”;
my $host = “localhost”;
my $port = “3306”;
my $user = “username”;
my $pw = “password”;
# DATA SOURCE NAME
my $dsn = “dbi:$platform:$database:$host:$port”;
# PERL DBI CONNECT
my $dbh = DBI->connect($dsn, $user, $pw) or die “Cannot connect: $DBI::errstr”;
# creating the “hollywood” database
$dbh->do(“CREATE TABLE IF NOT EXISTS actors(aid integer primary key, name text)”);
$dbh->do(“CREATE TABLE IF NOT EXISTS movies(mid integer primary key, title text)”);
$dbh->do(“CREATE TABLE IF NOT EXISTS actors_movies(id integer primary key, mid integer, aid integer)”);
#populating “actors” table
$dbh->do(“INSERT INTO actors(name) VALUES(‘Philip Seymour Hofman’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Kate Shindle’)”);
$dbh->do(“INSERT INTO actors(name) VALUES (‘Kelci Stephenson’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Al Pacino’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Gabrielle Anwar’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Patricia Arquette’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Gabriel Byrne’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Max von Sydow’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Ellen Burstyn’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Jason Miller’)”);
#populating “movies” table
$dbh->do(“INSERT INTO movies VALUES(1,’Capote’)”);
$dbh->do(“INSERT INTO movies VALUES(2,’Scent of a woman’)”);
$dbh->do(“INSERT INTO movies VALUES(3,’Stigmata’)”);
$dbh->do(“INSERT INTO movies VALUES(4,’Exorcist’)”);
$dbh->do(“INSERT INTO movies VALUES(5,’Hamsun’)”);
#populating “actorsMovies” table
$dbh->do(“INSERT INTO actors_movies VALUES(1,1,1)”);
$dbh->do(“INSERT INTO actors_movies VALUES(2,2,1)”);
$dbh->do(“INSERT INTO actors_movies VALUES(3,3,1)”);
$dbh->do(“INSERT INTO actors_movies VALUES(4,4,2)”);
$dbh->do(“INSERT INTO actors_movies VALUES(5,5,2)”);
$dbh->do(“INSERT INTO actors_movies VALUES(6,6,3)”);
$dbh->do(“INSERT INTO actors_movies VALUES(7,7,3)”);
$dbh->do(“INSERT INTO actors_movies VALUES(8,8,4)”);
$dbh->do(“INSERT INTO actors_movies VALUES(9,9,4)”);
$dbh->do(“INSERT INTO actors_movies VALUES(10,10,4)”);
$dbh->do(“INSERT INTO actors_movies VALUES(11,8,5)”);
print qq{“Hollywood” database created! \n };
$dbh->disconnect;
—————————————————query_hollywood.pl———————————————
#!/usr/bin/perl -w
use DBI;
use strict;
# CONFIG VARIABLES
my $platform = “SQLite”;
my $database = “hollywood.db”;
my $host = “localhost”;
my $port = “3306”;
my $user = “username”;
my $pw = “password”;
# DATA SOURCE NAME
my $dsn = “dbi:$platform:$database:$host:$port”;
# PERL DBI CONNECT
my $dbh = DBI->connect($dsn, $user, $pw) or die “Cannot connect: $DBI::errstr”;
# EXECUTE THE QUERY
my $query = “SELECT actors.name , movies.title FROM actors,movies,actors_movies WHERE actors.aid=actors_movies.aid and
movies.mid=actors_movies.mid”;
my $sth=$dbh->selectall_arrayref($query);
print “Actor Movie \n” ;
print “====================== ====================\n”;
foreach my $row (@$sth) {
my ($name, $title) = @$row;
### Print out the table metadata…
printf “%-23s %-23s \n”, $name, $title;
}
$dbh->disconnect;
—————————————————————————————–
Make the perl scripts executable like:
$ sudo chmod +x script.pl
And run them liket:
$ ./script.pl
The result of the query is:
Actor Movie
==================== ====================
Philip Seymour Hofman Capote
Philip Seymour Hofman Scent of a woman
Philip Seymour Hofman Stigmata
Kate Shindle Exorcist
Kate Shindle Hamsun
Very nice script, isn’t it?
I guess the DBI deserves further attention…
Sqlite is useful to create a database (one file, .db extension) used to store configuration data, used Miicrosoft, Skype, Banshee… Smart Phone applications….
The file extension .db stands for a whole database: it’s made by the software C library called Sqlite…
This is a great opportunity to learn about SQLITE and the Perl scripting languages… great, don’t you think?
Let’s kill two birds with a stone!
Install Sqlite3 and follow me:
On UBUNTU, DEBIAN, etc:
$ sudo apt-get install sqlite3
On OPEN-SUSE:
$ sudo zypper install sqlite3
On REDHAT, CentOS, or FEDORA:
$ yum install SQLite3
BASH EXAMPLE
Let’s create an example: a database called “test.db” by the (unix) shell by the following command:
:~$ sqlite3 test.db “create table if not exists user(id INTEGER PRIMARY KEY, name TEXT, surname TEXT);”
Let’s fill it:
:~$ sqlite3 test.db “insert into user (name, surname) values (‘linus’,’torvalds’);”
:~$ sqlite3 test.db “insert into user(name,surname) values(‘richard’, ‘stallman’)”;
To check it out:
:~$sqlite3 test.db “select * from n”;
The result is:
1|linus|torvalds
2|richard|stallman
Alternatively you can create a database entering the sqlite3 enviroment
$ sqlite3 test.db
SQLite version 3.0.8
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite>
In the sqlite3 enviroment you can use pure SQL statements to work with your database (in this case the test.db).
You can even change a few default settings to make the ouput of the commands look better. For example the column .mode and the .headers commands.They will last you exit the SQLite shell or change them to something else.
sqlite> .mode col
sqlite> .headers on
To see all the tables and views type:
sqlite> .tables
To see the databases that are currently open use the .databases command. It will show the main and temp databases and where they are on the system:
sqlite> .databases
To exit type .quit or .exit:
sqlite> .quit
Anyway it’s better to work in the shell prompt directly, that allows you to run bash scripts.
In this example the prompt is in your home/user directory. Check it out by the pwd command if you’re not sure…
PERL EXAMPLE
Make a file called test.pl :
$ touch test1.pl
Use an editor(ex. gedit under ubuntu and opensuse) or the cat test1.pl command to fill it with the following script:
#!/usr/bin/perl -w
use DBI;
use strict;
my $db = DBI->connect(“dbi:SQLite:test.db”, “”, “”) or die “couldn’t connect to db”.DBI->errstr;
$db->do(“CREATE TABLE IF NOT EXISTS user (id INTEGER PRIMARY KEY, name TEXT, surname TEXT)”);
$db->do(“INSERT INTO user\(name, surname) VALUES ( ‘linus’, ‘torvalds’)”);
$db->do(“INSERT INTO user\(name, surname) VALUES ( ‘richard’, ‘stallman’)”);
my $all = $db->selectall_arrayref(“SELECT * FROM USER”);
foreach my $row (@$all) {
my ($id, $name, $surname) = @$row;
print “$id|$name|$surname \n”;
}
$db->disconnect;
Alternatively you can make the file directly by the shell:
$ cat > test.pl
.. perl script content…
Type Ctlr+C to close the file and exit.
Now check the file content:
$> cat test.pl
Make the perl script executable:
$ sudo chmod +x test.pl
Then run the perl script simply:
$ ./test.pl
The result is:
1|linus|torvalds
2|richard|stallman
In the same folder where the script lies, you can find a file called “test.db”. That’s the Sqlite database. Just one file.
Yeah! So we’ve learned some Perl and SQLite, right?
Seamless Theme Keith, made by Altervista
Create a website and earn with Altervista - Disclaimer - Report Abuse - Privacy Policy - Customize advertising tracking