Solved

Sending script output to MySQL

Posted on 2006-07-19
7
335 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Author Comment

by:ashvillerob
Comment Utility
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
Comment Utility
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
Comment Utility
yep, just needed the backticks around "long", thanks to both of you !
0
 
LVL 39

Expert Comment

by:Adam314
Comment Utility
clockwatcher... good catch.

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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

On Microsoft Windows, if  when you click or type the name of a .pl file, you get an error "is not recognized as an internal or external command, operable program or batch file", then this means you do not have the .pl file extension associated with …
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 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

15 Experts available now in Live!

Get 1:1 Help Now