Posts Tagged ‘dbi’

Perl script for a Hollywood Sqlite database

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…

Categories
Links: