Solved

Sending script output to MySQL

Posted on 2006-07-19
7
345 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
execute pl/sql block from perl program 3 120
batch script for automated email 12 101
How to generate square thumbnail using perl 13 78
Regex rule to match two different url 5 60
I've just discovered very important differences between Windows an Unix formats in Perl,at least 5.xx.. MOST IMPORTANT: Use Unix file format while saving Your script. otherwise it will have ^M s or smth likely weird in the EOL, Then DO NOT use m…
Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now