Access your MySQL Database with Perl

One of the most common Perl-related questions at the SitePoint Forums is, "How do I access my database with Perl?" Perl can work with nearly every type of database on the market, but today I'll use MySQL as an example.

For the purposes of this demonstration, let's say we have a database named "shoes" with one table named "subscribers". Here's the layout of the table:

create table subscribers (
uid int not null primary key auto_increment,
username varchar(32) not null,
emailaddr varchar(32) not null
);
We will connect to MySQL with the DBI modules and the DBD::mysql database driver. These modules don't come with the standard Perl distribution, so you'll have to obtain them yourself. If you maintain your own server, follow the instructions below, but if you have a Web host, they should be able to take care of the installation for you, if they don’t already have the modules installed.

Install the Modules

To install the DBI and DBD::mysql modules on a Unix-like OS or Perl on Windows installed as part of Cygwin, run the following commands.

Note that you must have MySQL already installed or you will not be able to install the DBD::mysql module. Also, if you haven’t installed any other modules this way, you’ll be asked for configuration options. Luckily, these are self-explanatory. Once you've finished entering the configuration values, the module installation will start.

perl –MCPAN –e 'install DBI'
perl –MCPAN –e 'install DBD::mysql'
You'll see several screens of text fly by, but eventually it should return you to the friendly command prompt.

There will rarely be any errors, because these modules have been tested by thousands of people. However, if you are unlucky enough to encounter errors, a quick search on Google will probably find you the solution.

Work with the Database

Now that the modules and database drivers are installed, we can progress to the actual Perl code to work with the database. For now we'll just connect to the database.

# set the data source name
# format: dbi:db type:db name:host:port
# mysql’s default port is 3306
# if you are running mysql on another host or port,
#    you must change it
my $dsn = 'dbi:mysql:shoes:localhost:3306';
 
# set the user and password
my $user = 'user';
my $pass = 'pass';
 
# now connect and get a database handle  
my $dbh = DBI->connect($dsn, $user, $pass)
 or die "Can’t connect to the DB: $DBI::errstr\n";
OK, there were a lot of new things in that code. First, we defined the DSN, or data source name. This tells the DBI where to find your database server, and what database you will operate on. Then, we set the username and password to connect to the database as. Finally, we call DBI->connect to actually connect to the database (don’t forget to enter your own username and password!), and if the connection was unsuccessful, we print out an error. From that command, we get a database handle, which we will use to run queries on the database.

The execution of a query takes two steps: preparation and execution. First, you must prepare the query like this:

my $sth = $dbh->prepare('insert into subscribers(username, emailaddr)
values "jim", "jim@microsoft.com")');

Then you can execute it:

$sth->execute();
Because we ran an INSERT query, there weren’t any results to retrieve. But what if we used SELECT? How would we get the data? Like this:

my $sth = $dbh->prepare("select username, email from subscribers");
$sth->execute;
 
while(@row = $sth->fetchrow_array()) {
 print "$row[0]: $row[1]<br>";
}
As you can see, $sth->fetchrow_array returns an array of the results. If you like, you could write the loop like this to make it a bit more readable:

while(my($username, $email) = $sth->fetchrow_array()) {
 print "$username: $email<br>";
}
Instead of assigning the values to a normal array, this assigns the rows’ values each to a different variable.