Solved

Sending script output to MySQL

Posted on 2006-07-19
7
366 Views
Last Modified: 2010-05-18
I use the following script to GeoCode addresses and it sends the output to a flat text file. I need this script modified to send the output to a MySQL database instead of a flat text file.

#!/usr/bin/perl

use Geo::Coder::US;
use Data::Dumper;
use Time::HiRes qw(gettimeofday tv_interval);
use strict;
use warnings;

my $dbname = shift
    or die "Usage: $0 <path_to.db>\n";

Geo::Coder::US->set_db( $dbname );

open I,"<data.txt" or die "data.txt $!";
open O,">separate.text.file" or die "separate.text.file $!";

while( <I> ){
    my $t0  = [gettimeofday];
    my @res = Geo::Coder::US->geocode($_);
    my $interval = tv_interval($t0);
    warn $@ if $@;
    unless ($@) {
     print O Dumper(\@res), "\n";
     printf O "(Query took %.3f seconds)\n", $interval;
    }
}
close O;
close I;



The output of the script looks like this:

$VAR1 = [
          {
            'number' => 841,
            'lat' => '33.757351',
            'street' => 'Beaver Ridge',
            'state' => 'AL',
            'zip' => '35953',
            'city' => 'Ashville',
            'suffix' => '',
            'long' => '-86.319028',
            'type' => 'Dr',
            'prefix' => ''
          }
        ];


My database would have columns for "number", "street", "type","city", "state","zip", "suffix", "prefix","lat", and "long"
0
Comment
Question by:ashvillerob
  • 3
  • 3
7 Comments
 
LVL 39

Expert Comment

by:Adam314
ID: 17145382
You can use the DBI module.  There are plenty of examples here:
http://search.cpan.org/~timb/DBI-1.51/DBI.pm
0
 
LVL 1

Author Comment

by:ashvillerob
ID: 17145590
Thanks but for 500 points I  am looking for the exact code, not just a link :), BTW I do have the DBI module installed, if you would like to offer up a modification on my above code to use the DBI module.
0
 
LVL 39

Accepted Solution

by:
Adam314 earned 400 total points
ID: 17145987
I can't test any of this, but this should get you going.....



#!/usr/bin/perl

use Geo::Coder::US;
use Data::Dumper;
use Time::HiRes qw(gettimeofday tv_interval);
use DBI;    #This includes the module
use strict;
use warnings;

my $dbname = shift
    or die "Usage: $0 <path_to.db>\n";

Geo::Coder::US->set_db( $dbname );

#Make the connection, and prepare an INSERT statement.  You'll have to define yoru $dsn, $user, and $password
$dbh = DBI->connect($dsn, $user, $password, { RaiseError => 1, AutoCommit => 0 });
$sth = $dbh->prepare("INSERT INTO table_name (number, street, type, city, state, zip, suffix, prefix, lat, long) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

open I,"<data.txt" or die "data.txt $!";
open O,">separate.text.file" or die "separate.text.file $!";

while( <I> ){
    my $t0  = [gettimeofday];
    my @res = Geo::Coder::US->geocode($_);
    my $interval = tv_interval($t0);
    warn $@ if $@;
    unless ($@) {
     print O Dumper(\@res), "\n";
     #Execute the INSERT statement witht he values from $res
     $sth->execute( $res[0]->{'number'},
                    $res[0]->{'street'},
                    $res[0]->{'type'},
                    $res[0]->{'city'},
                    $res[0]->{'state'},
                    $res[0]->{'zip'},
                    $res[0]->{'suffix'},
                    $res[0]->{'prefix'},
                    $res[0]->{'lat'},
                    $res[0]->{'long'});
     printf O "(Query took %.3f seconds)\n", $interval;
    }
}
close O;
close I;
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:ashvillerob
ID: 17148969
Adam-

I get the following error:

C:\Documents and Settings\rwainwright\Desktop\GeoCoder\geocoder\geocoder\eg>scripttest.pl geocoder.db

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server v
ersion for the right syntax to use near 'long, type, prefix) VALUES ('841', '33.757351', 'Beaver Ridge', 'AL', '35953', '' at
 line 1 at C:\Documents and Settings\rwainwright\Desktop\GeoCoder\geocoder\geocoder\eg\scripttest.pl line 31, <I> line 1.
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server v
ersion for the right syntax to use near 'long, type, prefix) VALUES ('841', '33.757351', 'Beaver Ridge', 'AL', '35953', '' at
 line 1 at C:\Documents and Settings\rwainwright\Desktop\GeoCoder\geocoder\geocoder\eg\scripttest.pl line 31, <I> line 1.

The only modifications I made to the script was commenting out use strict;

and I rearrange the order of the database fields, here is the edited version:
#!C:\Perl\bin\perl

use Geo::Coder::US;
use Data::Dumper;
use Time::HiRes qw(gettimeofday tv_interval);
use DBI;    #This includes the module
#use strict;
use warnings;

my $dbname = shift
    or die "Usage: $0 <path_to.db>\n";

Geo::Coder::US->set_db( $dbname );

#Make the connection, and prepare an INSERT statement.  You'll have to define your $dsn, $user, and $password

$dbh = DBI->connect("DBI:mysql:database=test;host=localhost", "xxxxx", "xxxxxx", {'RaiseError' => 1});
$sth = $dbh->prepare("INSERT INTO zip_codes (number, lat, street, state, zip, city, suffix, long, type, prefix) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

open I,"<data.txt" or die "data.txt $!";
open O,">output.txt" or die "output.txt $!";

while( <I> ){
    my $t0  = [gettimeofday];
    my @res = Geo::Coder::US->geocode($_);
    my $interval = tv_interval($t0);
    warn $@ if $@;
    unless ($@) {
     print O Dumper(\@res), "\n";
     #Execute the INSERT statement with the values from $res
     $sth->execute( $res[0]->{'number'},
                    $res[0]->{'lat'},
                    $res[0]->{'street'},
                    $res[0]->{'state'},
                    $res[0]->{'zip'},
                    $res[0]->{'city'},
                    $res[0]->{'suffix'},
                    $res[0]->{'long'},
                    $res[0]->{'type'},
                    $res[0]->{'prefix'});
     printf O "(Query took %.3f seconds)\n", $interval;
    }
}
close O;
close I;


Even though it errors out, it does create the output file correctly the file results are below:

$VAR1 = [
          {
            'number' => 841,
            'lat' => '33.757351',
            'street' => 'Beaver Ridge',
            'state' => 'AL',
            'zip' => '35953',
            'city' => 'Ashville',
            'suffix' => '',
            'long' => '-86.319028',
            'type' => 'Dr',
            'prefix' => ''
          }
        ];


Here is the format of my input file:

841 Beaver Ridge Drive Ashville AL 35953






0
 
LVL 25

Assisted Solution

by:clockwatcher
clockwatcher earned 100 total points
ID: 17151605
Not for points...

long is a reserved word in MySQL.  You need to quote it.  Pretty sure backticks are the quote operator for MySQL:

   INSERT INTO zip_codes (number, lat, street, state, zip, city, suffix, `long`, type, prefix) ...

If that doesn't work, give double quotes a try:

   INSERT INTO zip_codes (number, lat, street, state, zip, city, suffix, "long", type, prefix) ...
0
 
LVL 1

Author Comment

by:ashvillerob
ID: 17157299
yep, just needed the backticks around "long", thanks to both of you !
0
 
LVL 39

Expert Comment

by:Adam314
ID: 17157572
clockwatcher... good catch.

I don't do much with mysql, and had no idea why it wasn't working...
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many time we need to work with multiple files all together. If its windows system then we can use some GUI based editor to accomplish our task. But what if you are on putty or have only CLI(Command Line Interface) as an option to  edit your files. I…
I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question