Matthew Daly's Blog

I'm a web developer in Norfolk. This is my blog...

15th October 2010 8:39 pm

Getting Work Done With Perl

After my initial struggles with Perl, I now think I’m really starting to get to grips with the language. I generally find it a pain when you have to learn by building small but basically useless scripts - I always do best when building something useful.

As one of the exercises for my studies I had to open a database connection to a Microsoft Access database, but I wanted to do the exercise in Ubuntu (I’ve always preferred using Unix-like operating systems for programming, and thanks to apt-get it’s a lot less grief installing additional libraries and modules as you need them) and couldn’t get Perl to connect to the database properly, so I resolved to export it to either MySQL or SQLite.

I was able to export it to MySQL in the end using mdbtools, but I wasn’t entirely happy with the end result. I resorted to re-exporting the data as a CSV file, then resolved to write a small Perl script to read the file, parse it using a regular expression to obtain the necessary information, then insert it into a new SQLite database.

Here’s what I came up with:

#!/usr/bin/perl -w
use strict;
use DBI;
my $db = "dbi:SQLite:backend.db";
if (!(-e "backend.db"))
{
print "Database does not exist. Creating it...";
# Create the database
my $dbh = DBI->connect($db) or die "Error in connecting to database! $DBI::errstr";
my $createdb = $dbh->do("CREATE TABLE CARS( ID INTEGER PRIMARY KEY, YEAR INTEGER, MAKE VARCHAR(30), MODEL VARCHAR(30), COLOR VARCHAR(30), PRICE INTEGER);");
$dbh->disconnect();
}
open(READFILE, "cartable.csv");
while()
{
unless($_ =~ m/id,/)
{
m/\d+\,(\d{4})\,\"(\w+)\"\,\"(\w+)\"\,\"(\w+)\"\,\"(\d+)\"/;
my $year = $1;
my $make = $2;
my $model = $3;
my $color = $4;
my $price = $5;
my $dbh2 = DBI->connect($db) or die "Error in connecting to database! $DBI::errstr";
my $insertdb = $dbh2->do("INSERT INTO CARS (YEAR, MAKE, MODEL, COLOR, PRICE) VALUES (\"$year\", \"$make\", \"$model\", \"$color\", \"$price\");");
$dbh2->disconnect;
}
}
close(READFILE);
print "Write completed!\n";
print "To demonstrate it works, we'll run a SELECT query against the database...\n";
# Read the database
my $readdb = DBI->connect($db);
my $dbselect = $readdb->prepare("SELECT * FROM CARS;");
$dbselect->execute;
# Print the results
print "ID\tYear\tMake\tModel\tColor\tPrice\n";
while(my @row = $dbselect->fetchrow_array)
{
print "$row[0]\t$row[1]\t$row[2]\t$row[3]\t$row[4]\t$row[5]\n";
}
# Close the connection
$readdb->disconnect;

Apologies for the fact that the indentation doesn’t seem to have copied across from Vim very well (can anyone recommend a good WordPress plugin for displaying code, none of the ones I’ve tried seem to be any good?). It works well, and it’s also helped me grasp Perl’s database API better.

I think I’ve got a better idea now of what Python and Perl are best at and when to use each. Perl is a great language, but the fact that a lot of it is implicit makes it a little harder to pick up at first than Python - for instance, the default variable, which is quite a good idea, but takes a little getting used to. Its regex support is great, and I like the database API, but I would find it a lot harder to do any object-oriented programming in Perl than in Python (which I guess is why Moose exists). I’ve found Perl very useful for quick and dirty scripts and as a glue language, but for longer scripts Python seems the better choice.

Recent Posts

Using Phpiredis With Laravel

Simple Fuzzy Search With Laravel and Postgresql

A Generic PHP SMS Library

Installing Nginx Unit on Ubuntu

Making Internal Requests With Laravel

About me

I'm a web and mobile app developer based in Norfolk. My skillset includes Python, PHP and Javascript, and I have extensive experience working with CodeIgniter, Laravel, Django, Phonegap and Angular.js.