From: Mark Ericson <mark.ericson@...>
Date: 2005-12-15T12:59:59+09:00
Subject: Re: US Zipcode API for Ruby?

------=_Part_673_2012695.1134619192078
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Excellent!  You beat me to it.  My approach to import was somewhat
different, your probably has the advantage of a transaction per row.

require 'csv'
require 'dbi'

DBI.connect("DBI:ADO:Provider=3DSQLOLEDB;Data Source=3Dlocalhost;Initial
Catalog=3DUSZipCodes;User Id=3Dtest;Password=3Dtest") do | dbh |

    sql =3D "INSERT INTO ZipData (zipcode, city, state, latitude, longitude=
,
timezone, dst) VALUES (?, ?, ?, ?, ?, ?, ?)"
    dbh.prepare(sql) do | sth |
      begin
        rdr =3D CSV.open("zipcode.csv", "r")
        header =3D rdr.shift  # skip header row
        rdr.each do |row|
          sth.execute(row[0], row[1], row[2], row[3], row[4], row[5],
row[6])
        end
      ensure
        CSV.close unless CSV.nil?
      end
    end
end



On 12/14/05, Paul Duncan <pabs@pablotron.org> wrote:
>
> Using Ruby and SQLite3:
>
>   pabs@halcyon:~/proj/zip> ./import.rb zipcode.{csv,db}
>   pabs@halcyon:~/proj/zip> ./find.rb zipcode.db 22003 3
>   "city","state","zip","distance (mi)"
>   "Annandale","VA","22003","0.0"
>   "Springfield","VA","22161","1.62363604423677"
>   "Springfield","VA","22151","1.87190097838136"
>   "Falls Church","VA","22042","2.97362028549975"
>
> Here's the code for each piece (also available at the URL
> http://pablotron.org/files/zipfind.tar.gz):
>
>   ---- import.rb ----
>   #!/usr/bin/env ruby
>
>   # load libraries
>   require 'rubygems' rescue nil
>   require 'sqlite3'
>
>   # constants
>   SCAN_RE =3D
> /"(\d{5})","([^"]+)","(..)","([\d.-]+)","([\d.-]+)","([\d-]+)","(\d)"/
>   SQL =3D "INSERT INTO zips(zip, city, state, lat, long, timezone, dst)
>                 VALUES (?, ?, ?, ?, ?, ?, ?)"
>   TABLE_SCHEMA =3D "CREATE TABLE zips (
>     id        INTEGER     NOT NULL PRIMARY KEY,
>
>     zip       VARCHAR(5)  NOT NULL,
>     city      TEXT        NOT NULL,
>     state     VARCHAR(2)  NOT NULL,
>     lat       FLOAT       NOT NULL,
>     long      FLOAT       NOT NULL,
>     timezone  INTEGER     NOT NULL,
>     dst       BOOLEAN     NOT NULL
>   );"
>
>
>   # handle command-line arguments
>   unless ARGV.size =3D=3D 2
>     $stderr.puts "Usage: #$0 <csv> <db>"
>     exit -1
>   end
>   csv_path, db_path =3D ARGV
>
>   # load database, create zip table and prepared statement
>   db =3D SQLite3::Database.new(db_path)
>   db.query(TABLE_SCHEMA)
>   st =3D db.prepare(SQL)
>
>   # parse CSV and add each line to the database
>   db.transaction {
>     File.read(csv_path).scan(SCAN_RE).each { |row| st.execute(*row) }
>   }
>

------=_Part_673_2012695.1134619192078--