Database - Using Perl With MySQL

SignalQ Sites:
NetAdminTools - Coprolite - NoNIC - SpotBridge - NAW
RoboCoop - AreWeDown - SolarPower - SysAdminTools
Xfig - Gold Loaf - GeekPapa - FixGMC - MCJ - FixRambler

Categories:
·GNU/Linux
·Homebrew designs
·Perl
·Administration
·Backup/Recovery
·Bugs/Fixes
·Certification
·Database
·Email
·File/Print
·Hardware
·Information Grab Bag
·Interoperability
·GNU/Linux ABCs
·Monitoring
·Name Resolution
·Network Services
·Networking
·Remote Control
·Security
·Desktop
·Web
·BSD
·Solaris
·GIAGD
·REALbasic
·All Categories

Using Perl With MySQL
Topic: Database Posted:2002-12-27
Printer Friendly:

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.

Please read our Terms of Use
Microsoft, Windows, Windows XP, Windows 2003, Windows 2000, and NT are either trademarks or registered trademarks of Microsoft Corporation. NetAdminTools.com is not affiliated with Microsoft Corporation. Linux is a registered trademark of Linus Torvalds, and refers to the Linux kernel. The operating system of most distributions that contain the Linux kernel is GNU/Linux. All logos and trademarks in this site are property of their respective owner. Copyright 1997-2008 NetAdminTools.com

Created by:

MCJ CMS