How to access MySQL database using Perl

by on September 7, 2006 · 12 comments· LAST UPDATED September 19, 2008

in

Q. How do I access my MySQL database server using Perl programming language?

A. DBI is a generic interface for many databases. That means that you can write a script that works with many different database engines without change. You need a DataBase Driver (DBD) defined for each database type. For MySQL, this driver is called DBD::mysql.

So you can connect Perl to your MySQL database server using the DBI Perl module. Here is small program.

Sample Perl code

Make sure you replace database name, MySQL server hostname, username and password according to your setup.

#!/usr/bin/perl -w
 
use DBI;
 
print "Content-type: text/html\n\n";
 
## mysql user database name
$db ="mysql";
## mysql database user name
$user = "vivek";
 
## mysql database password
$pass = "myPassword";
 
## user hostname : This should be "localhost" but it can be diffrent too
$host="localhost";
 
## SQL query
$query = "show tables";
 
$dbh = DBI->connect("DBI:mysql:$db:$host", $user, $pass);
$sqlQuery  = $dbh->prepare($query)
or die "Can't prepare $query: $dbh->errstr\n";
 
$rv = $sqlQuery->execute
or die "can't execute the query: $sqlQuery->errstr";
 
print "<h3>********** My Perl DBI Test ***************</h3>";
print "<p>Here is a list of tables in the MySQL database $db.</p>";
while (@row= $sqlQuery->fetchrow_array()) {
my $tables = $row[0];
print "$tables\n<br>";
}
 
$rc = $sqlQuery->finish;
exit(0);

Save and upload above program.

TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

{ 12 comments… read them below or add one }

1 Ron Lusk September 19, 2008 at 9:59 pm

I’ve looked at a lot of sample code trying to learn how to successfully get extract any information from a database and this has been the first that actually worked! Kudos. Very helpful.

Reply

2 Stone Man March 10, 2009 at 4:48 am

Hi Nixcraft,

Thanks for this code but i got the following:

– I copy the code and paste it in this file checkdb.sh.
– And run chmod +x checkdb.sh
– Then run ./checkdb.sh

Then the following occured:

Name “main::rv” used only once: possible typo at /usr/bin/check.db line 25.
Name “main::rc” used only once: possible typo at /usr/bin/check.db line 42.
Content-type: text/html

********** My Perl DBI Test ***************

Here is a list of tables in the MySQL database mysql.

columns_priv
db
event
func
general_log
help_category
help_keyword
help_relation
help_topic
host
ndb_binlog_index
plugin
proc
procs_priv
servers
slow_log
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user
[root@drbl-01 ~]#

Currently installed Perl Modules:

[root@drbl-01 DBD-mysql-4.010]# instmodsh
Available commands are:
l – List all installed modules
m – Select a module
q – Quit the program
cmd? l
Installed modules are:
DBD::mysql
DBI
Perl
cmd? q

ANy suggestion on how to fix this?
Thanks again!

Stone

Reply

3 medoix November 20, 2009 at 7:33 am

Stone

remove $rv = and $rc = they are not needed on those lines.

Reply

4 Anitha December 3, 2009 at 5:15 am

I have to synchronize bugzilla which uses mysql and HP quality centre which uses mssql .. i’m tryin to do this in perl.. As a fresher i’m not sure wat perl can do .. can anyone suggest me

Reply

5 Lina October 6, 2010 at 11:33 pm

This really does work. After 3 days of searching this is the first script that works.

THANK YOU!!!!!!!

Reply

6 Abby December 1, 2010 at 7:34 am

Works beautifully! Thank you :)

Reply

7 ahmed September 27, 2011 at 8:48 am

It’s working very well
thank you.

Reply

8 UncaAlby October 27, 2011 at 7:14 am

Works like a champ!

My only significant modifications were “use strict” which means adding “my” to declare all the variables.

It would be nice if it also had an example of adding parameters to the prepared statement. I happened to look that up separately, which I’m adding to this comment in case someone else finds it useful.

Example: list every row where the user_id starts with “a”.

$sql = "select user_id from users where user_id like ?";
$sth = $dbh->prepare($sql) or die(DBI->errstr);
$sth->bind_param(1, "a%");
$rv = $sth->execute or die(DBI->errstr);
print "User ID's that start with 'a'\n";
while (my @row = $sth->fetchrow_array()) {
  my $tables = $row[0];
  print "$tables\n";
}
$rc = $sth->finish;

Results (from my table):

User ID’s that start with ‘a’

admin
alameda
arthur

Reply

9 Cory June 27, 2012 at 7:54 pm

Thank you!

Reply

10 slyft May 29, 2013 at 11:49 am

What extention should I use while saving the program? An does it need any compiler or IDM ? Thanks

Reply

11 pavan June 25, 2013 at 6:53 am

Thanks a lot,
I have been searching for this, past 4 days.

Can you please share script to connect excel file into perl.
Please…..

Thanks in Advance.

Reply

12 khinelay August 11, 2014 at 1:32 pm

I’ve got an error when I installed the DBI::mysql via cpan.I am using ec2(ubuntu 14) and rds(mysql). In my ec2 I’ve already DBI via cpan. When I install DBI::mysql, I’ve got an error.
cpan[1]> install DBI::mysql
Reading ‘/home/ubuntu/.cpan/Metadata’
Database was generated on Sun, 10 Aug 2014 14:06:13 GMT
Warning: Cannot install DBI::mysql, don’t know what it is.
Try the command

i /DBI::mysql/

to find objects with matching identifiers.
so i type i /DBI::mysql/ and I’ve got this message

nolock_cpan> i /DBI::mysql/
Module < Ambrosia::DataProvider::DBI::mysql (KNM/Ambrosia-0.010.tar.gz)
Module < AnyEvent::DBI::MySQL (POWERMAN/AnyEvent-DBI-MySQL-1.0.5.tar.gz)
Module < AnyEvent::DBI::MySQL::db (POWERMAN/AnyEvent-DBI-MySQL-1.0.3.tar.gz)
Module < AnyEvent::DBI::MySQL::st (POWERMAN/AnyEvent-DBI-MySQL-1.0.3.tar.gz)
Module < AnyEvent::DBI::MySQL::st::ready (POWERMAN/AnyEvent-DBI-MySQL-1.0.3.tar.gz)
Module < Audio::DB::Adaptor::dbi::mysql (TWH/Audio-DB-0.01.tar.gz)
Module < Bio::DB::DBI::mysql (CJFIELDS/BioPerl-DB-1.006900.tar.gz)
Module < Bio::DB::GFF::Adaptor::dbi::mysql (CJFIELDS/BioPerl-1.6.924.tar.gz)
Module < Bio::DB::GFF::Adaptor::dbi::mysqlace (CJFIELDS/BioPerl-1.6.924.tar.gz)
Module < Bio::DB::GFF::Adaptor::dbi::mysqlcmap (CJFIELDS/BioPerl-1.6.924.tar.gz)
Module < Bio::DB::GFF::Adaptor::dbi::mysqlopt (CJFIELDS/BioPerl-1.6.924.tar.gz)
Module < Bio::DB::SeqFeature::Store::DBI::mysql (CJFIELDS/BioPerl-1.6.924.tar.gz)
Module < CHI::t::Driver::DBI::mysql (JSWARTZ/CHI-Driver-DBI-1.24.tar.gz)
Module < Class::DBI::mysql (TMTM/Class-DBI-mysql-1.00.tar.gz)
Module < Class::DBI::mysql::FullTextSearch (TMTM/Class-DBI-mysql-FullTextSearch-1.01.tar.gz)
Module < DBIx::Class::Fixtures::DBI::mysql (JJNAPIORK/DBIx-Class-Fixtures-1.001021.tar.gz)
Module < DBIx::Class::Schema::Loader::DBI::mysql (ILMARI/DBIx-Class-Schema-Loader-0.07041.tar.gz)
Module < DBIx::Class::Storage::DBI::mysql (RIBASUSHI/DBIx-Class-0.08270.tar.gz)
Module < DBIx::Class::Storage::DBI::mysql::backup (NGS/DBIx-Class-Storage-DBI-mysql-backup-0.04.tar.gz)
Module < DBIx::Skinny::Schema::Loader::DBI::mysql (NEKOYA/DBIx-Skinny-Schema-Loader-0.25.tar.gz)
Module < DBO::Handle::DBI::mysql (ABW/DBO-0.01.tar.gz)
Module < JAF::DBI::mysql (GRISHACE/Apache-JAF-0.21.tar.gz)
Module < NoSQL::PL2SQL::DBI::MySQL (TQISJIM/NoSQL-PL2SQL-1.21.tar.gz)
Module < NoSQL::PL2SQL::DBI::MySQL::Schema (TQISJIM/NoSQL-PL2SQL-1.21.tar.gz)
Module < NoSQL::PL2SQL::DBI::MySQL::Schema::index (TQISJIM/NoSQL-PL2SQL-1.21.tar.gz)
Module < NoSQL::PL2SQL::DBI::MySQL::Schema::index::column (TQISJIM/NoSQL-PL2SQL-1.21.tar.gz)
Module < NoSQL::PL2SQL::DBI::MySQL::Schema::table (TQISJIM/NoSQL-PL2SQL-1.21.tar.gz)
Module < NoSQL::PL2SQL::DBI::MySQL::Schema::table::column (TQISJIM/NoSQL-PL2SQL-1.21.tar.gz)
Module < ORM::Db::DBI::MySQL (AKIMOV/ORM-0.85.1.tar.gz)
Module < ORM::Db::DBI::MySQLemTa (AKIMOV/ORM-0.85.1.tar.gz)
Module < RDF::Trine::Store::DBI::mysql (GWILLIAMS/RDF-Trine-1.009.tar.gz)
Module < SPOPS::DBI::MySQL (CWINTERS/SPOPS-0.87.tar.gz)
Module < SQL::Translator::Parser::DBI::MySQL (FREW/SQL-Translator-0.11018.tar.gz)
Module < SimpleDBI::mysql (ABBYPAN/SimpleDBI-0.01.tar.gz)
34 items found

Reply

Leave a Comment

Tagged as: , , , , , , , , , , , , , , , ,

Previous Faq:

Next Faq: