Database - Using Perl With MySQL

Using Perl With MySQL

To communicate with MySQL via Perl, use the DBI and DBD-MySQL modules. With Red Hat 8.0, just install perl-DBD-MySQL-2.1017-3 and perl-DBI-1.30-1 off of the CDs. Why would you want to do such a thing? Well, say you had a tab delimited text file:

sam     33      red
sarah   32      blue
ed        23      black
russell 13      yellow

but the database lists the fields in a different order, so we can't do a simple import. We can use Perl to parse out these fields, and write the reordered data back to a MySQL database using this script:

use DBI;
use DBD::mysql;
$dsn = "DBI:mysql:people:localhost";
$dbh = DBI->connect($dsn,"root","password",{RaiseError=>1});
while(<>){
@line=split("\\t",$_);
print @line[0]." ".@line[1]." ".@line[2];
$sth = $dbh->prepare ( q{
INSERT INTO specifics (color,name,age) VALUES (?,?,?)
});
($name, $age, $color)=@line;
$sth->execute($color,$name,$age);
$sth->finish
}
$dbh->disconnect();

If we name the table sampledb and the perl script above import.pl and execute perl import.pl < sampledb, then the table specifics in the database people would be updated with the rows in the text file; however, the fields in the records will be shuffled to match the records in the table. Note that it quite easy using this method to convert all of the random data embedded in spreadsheets into a real database. Perl has other modules that will allow it to connect to other databases besides MySQL.

This article comes from NetAdminTools:
http://www.netadmintools.com/

The URL for this story is:
http://www.netadmintools.com/art169.html

Copyright 1997-2007 NetAdminTools.com. Read our Terms of Use.