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:

1#!/usr/bin/perl -w
2
3use strict;
4use DBI;
5
6my $db = "dbi:SQLite:backend.db";
7
8if (!(-e "backend.db"))
9{
10   print "Database does not exist. Creating it...";
11
12   # Create the database
13   my $dbh = DBI->connect($db) or die "Error in connecting to database! $DBI::errstr";
14   my $createdb = $dbh->do("CREATE TABLE CARS( ID INTEGER PRIMARY KEY, YEAR INTEGER, MAKE VARCHAR(30), MODEL VARCHAR(30), COLOR VARCHAR(30), PRICE INTEGER);");
15   $dbh->disconnect();
16}
17
18open(READFILE, "cartable.csv");
19
20while()
21{
22   unless($_ =~ m/id,/)
23   {
24      m/\d+\,(\d{4})\,\"(\w+)\"\,\"(\w+)\"\,\"(\w+)\"\,\"(\d+)\"/;
25      my $year = $1;
26      my $make = $2;
27      my $model = $3;
28      my $color = $4;
29      my $price = $5;
30
31      my $dbh2 = DBI->connect($db) or die "Error in connecting to database! $DBI::errstr";
32      my $insertdb = $dbh2->do("INSERT INTO CARS (YEAR, MAKE, MODEL, COLOR, PRICE) VALUES (\"$year\", \"$make\", \"$model\", \"$color\", \"$price\");");
33      $dbh2->disconnect;
34   }
35}
36close(READFILE);
37
38print "Write completed!\n";
39print "To demonstrate it works, we'll run a SELECT query against the database...\n";
40
41# Read the database
42my $readdb = DBI->connect($db);
43my $dbselect = $readdb->prepare("SELECT * FROM CARS;");
44$dbselect->execute;
45
46# Print the results
47print "ID\tYear\tMake\tModel\tColor\tPrice\n";
48while(my @row = $dbselect->fetchrow_array)
49{
50   print "$row[0]\t$row[1]\t$row[2]\t$row[3]\t$row[4]\t$row[5]\n";
51}
52
53# Close the connection
54$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.