Posts Tagged ‘perl’

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!

Perl script using LWP module

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:


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).


#!/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"; }

Perl script for a Hollywood Sqlite database

Let’s analyze the following perl scripta “”, to create and populate a simple database about  movies, and “” to execute a simple SELECT-FROM-WHERE query on it.


#!/usr/bin/perl -w

use DBI;
use strict;

my $platform = “SQLite”;
my $database = “hollywood.db”;
my $host = “localhost”;
my $port = “3306”;
my $user = “username”;
my $pw = “password”;

my $dsn = “dbi:$platform:$database:$host:$port”;

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 };



#!/usr/bin/perl -w

use DBI;
use strict;

my $platform = “SQLite”;
my $database = “hollywood.db”;
my $host = “localhost”;
my $port = “3306”;
my $user = “username”;
my $pw = “password”;

my $dsn = “dbi:$platform:$database:$host:$port”;

my $dbh = DBI->connect($dsn, $user, $pw) or die “Cannot connect: $DBI::errstr”;

my $query = “SELECT , movies.title  FROM actors,movies,actors_movies WHERE actors.aid=actors_movies.aid and

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;




Make the perl scripts executable like:
$ sudo chmod +x

And run them liket:
$ ./

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…

First steps in Sqlite and Perl

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:
$ sudo apt-get install sqlite3
$ sudo zypper install sqlite3
$ yum install SQLite3

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:

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 “;”

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…


Make a file called :
$ touch

Use an editor(ex. gedit under ubuntu and opensuse) or the cat 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(“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”;



Alternatively you can make the file directly by the shell:
$ cat >
.. perl script content…

Type Ctlr+C to close the file and exit.
Now check the file content:
$> cat

Make the perl script executable:
$ sudo chmod +x
Then run the perl script simply:
$ ./
The result is:

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?