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.