Databases Reference
In-Depth Information
Binding Variables to a Query
Consider the script we wrote earlier in “Reading in values from a file” in Chapter 16 to
read in a list of animals and their counts from the
animals.csv
file of comma-separated
values, and load them into the
Animals
hash. We've reproduced the main part of the
script in Example 17-2.
Example 17-2. Perl script to load data from a CSV file into the AnimalDB database
#!/usr/bin/perl
use strict;
# If the user hasn't provided one command-line argument, provide a
# helpful error message.
if(@ARGV!=1)
{
die("Syntax: $0 [Input file]\n");
}
# Open the file specified on the command line; if we can't open it,
# print an error message and stop.
open(INPUTFILE, $ARGV[0])
or
die("Failed opening $ARGV[0]\n");
my %Animals;
# Read in from input file line by line; each line is
# automatically placed in $_
while(<INPUTFILE>)
{
# Remove the newline at the end of the line
chomp($_);
# Split the line by commas and load into the AnimalsData array
my @AnimalsData=split(",", $_);
# Assign the text before the first comma to the name
my $AnimalName=$AnimalsData[0];
# Assign the text between the first comma and the second comma
# (if any) to the count
my $AnimalCount=$AnimalsData[1];
# Add an entry to the Animals hash for this animal name and
# count pair:
$Animals{$AnimalName}=$AnimalCount;
}
# Close the input file
close(INPUTFILE);
Let's modify the script to load the data into a MySQL table. First, using the MySQL
monitor, create a new
AnimalDB
database and a new
Animals
table:
