Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sending script output to MySQL

Posted on 2006-07-19
7
Medium Priority
?
379 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 1600 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 400 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
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…
Six Sigma Control Plans

715 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