Using Perl with MySQL - Learning MySQL - page 552

$sth->finish();
$dbh->disconnect();
Importing and Exporting Data
From time to time, you may need to transfer data into the database from external
sources, or to generate data in a format that other applications can use. A common file
format for this is the comma-separated values (CSV) format discussed in “Loading Data
from Comma-Delimited Files” in Chapter 8. Data import and export is one of the areas
in which Perl is very strong, and programs in Perl can read and write data in a large
number of formats. For example, you can generate plain text, HTML, XML, or Rich
Text Format (RTF) documents. RTF documents are more complex but can contain
formatting instructions that most word processors understand. There are even Perl
modules to process binary (nontext) formats, such as the Microsoft Excel spreadsheet
file format.
Earlier, in “Binding Variables to a Query,” we explained how to import data from a
CSV file. Let's now look at an example to export data from our Animals database to a
CSV file. All we need to do is to use the print statement to write to the output file, with
the data separated by a comma, as shown in Example 17-6.
Example 17-6. Perl script to export data from the Animals database, using binding
#!/usr/bin/perl
use DBI;
use strict;
# If the user hasn't provided any command-line arguments, provide a
# helpful error message.
if(@ARGV!=1)
{
die("Syntax: $0 [Output file]\n");
}
my $DB_Database="AnimalDB";
my $DB_Username="root";
my $DB_Password=" the_mysql_root_password ";
my $dbh=DBI->connect( "DBI:mysql:host=localhost;database=$DB_Database",
"$DB_Username", "$DB_Password", {PrintError=>0, RaiseError=>0})
or
die("Failed connecting to the database ".
"(error number $DBI::err): $DBI::errstr\n");
my $Query="SELECT Name, SUM(Count) FROM Animals GROUP BY Name";
my $sth = $dbh->prepare($Query);
$sth->execute();
# Bind query results to variables
my $Animal_Name;
my $Animal_Count;