How to Connect to MySQL from Perl and Select Records with Example

Q: How do I connect to a MySQL database from a perl program? I like to connect to an existing mysql database and select records from a particular table. Can you explain with a simple working example?

A: You should use perl DBI module to connect to a MySQL database as explained below.

If you don’t have perl DBI and DBD::mysql module installed, install perl module using cpan as we discussed earlier.

# perl -MCPAN -e shell
cpan> install DBI
cpan> install DBD::mysql

On a very high level, you’ll have to do the following three steps to connect to a MySQL database and get data.

1. Connect to the MySQL Database

In the DBI module, you’ll use the connect function as shown below.

$dbc = DBI->connect($source, $username, $password)

DBI->connect function takes the following three arguments:

• $source – This is in the format of “DBI:mysql:[database]:[hostname]”. Replace the [database] and [hostname] with values from your system. In the example shown below, it is connecting to the database called “mycompany” that is running on the localhost.
• $username – The username that is used to connect to the MySQL database.
• $password – The password for the above username.

You should store the return value of the DBI->connect to a variable, which you’ll use to prepare the sql statement later.

2. Prepare the SQL Statement

Once you’ve connected, you’ll use the prepare the sql statement using the prepare command. You’ll call the prepare command by using the variable that was returned by the DBI->connect function.

The following command will prepare the select sql command.

$sql = $dbc->prepare("select id, name from employee");

You should store the return value of the prepare command to a variable, which you’ll use to execute the sql command.

3. Execute the SQL Statement

Once you’ve prepared the sql statement, you’ll just execute the statement using execute command. You’ll call the execute command by using the variable that was returned by the prepare function.

$out = $sql->execute()

4. Loop through the Records

Once you’ve executed the statement, you’ll loop through records using the fetchrow_array function. You’ll call the fetchrow_array function using the variable that was returned by the prepare function.

while (($id, $name) = $sql->fetchrow_array())
{
 print "Id: $id Name: $name\n";
}

5. Working MySQL Perl Example

The following example perl code will connect a MySQL database and select the records from the employee table and print it.

$ vi connect.pl
#!/usr/bin/perl

use DBI;

$source = "DBI:mysql:mycompany:localhost";
$username = "root";
$password = "MySecretPassword";

$dbc = DBI->connect($source, $username, $password)
or die "Unable to connect to mysql: $DBI::errstr\n";

$sql = $dbc->prepare("select id, name from employee");

$out = $sql->execute()
or die "Unable to execute sql: $sql->errstr";

while (($id, $name) = $sql->fetchrow_array())
{
 print "Id: $id Name: $name\n";
}

When you execute the above connect.pl perl code, it will display the records from the employee table as shown below.

$ ./connect.pl
Id: 100 Name: Thomas
Id: 200 Name: Jason
Id: 300 Name: Sanjay
Id: 400 Name: Nisha
Id: 500 Name: Randy
Id: 501 Name: Ritu