≡ Menu

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

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 2 comments… add one }

  • Bhagyaraj November 12, 2013, 11:09 pm

    Nice article, sorry little late respond, working well.
    Please provide more articles on Database and GUI interface of Web browser using Perl.

  • David Busby December 3, 2013, 6:51 am

    You should add an example using BIND Parameters for MYSQL. This is different between say Oracle and MySQL but besides being a best practice for security binding the SQL Statements with the parameters will make the best use of memory and other resources within the database. I have seen applications consume half the amount of memory after converting to binds. Let me know if you want an example.

Leave a Comment