Solved

Sending script output to MySQL

Posted on 2006-07-19
7
368 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Perl output collect 7 70
Perl troubleshooting error. Base64 bootstrap parameter 5 79
Add additional column to .csv using Perl. 8 177
Perl string replace for refred url 9 73
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…
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
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…

752 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