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

Mutation Testing With Infection

Switching from Vim to Neovim

Better Strings in PHP

Forcing SSL in Codeigniter

Logging to the ELK Stack 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.