Newbie question concerning ruby and mysql

ruby-talk
1

Hi, I'm taking this flat file and putting it into a mysql table.
The question is, given the code I will be working from, what's the Ruby way
to skip the first line?

Here's the table
"zip code", "state abbreviation", "latitude", "longitude", "city", "state"
"35004", "AL", " 33.606379", " -86.50249", "Moody", "Alabama"
"35005", "AL", " 33.592585", " -86.95969", "Adamsville", "Alabama"
"35006", "AL", " 33.451714", " -87.23957", "Adger", "Alabama"
"35007", "AL", " 33.232422", " -86.80871", "Alabaster", "Alabama"
"35010", "AL", " 32.903432", " -85.92669", "Alexander City", "Alabama"
"35014", "AL", " 33.355960", " -86.27720", "Alpine", "Alabama"
"35016", "AL", " 34.323715", " -86.49278", "Arab", "Alabama"
"35019", "AL", " 34.292540", " -86.63505", "Baileyton", "Alabama"
"35020", "AL", " 33.405559", " -86.95141", "Bessemer", "Alabama"
"35022", "AL", " 33.346817", " -86.95252", "Bessemer", "Alabama"
"35023", "AL", " 33.443039", " -87.01930", "Bessemer", "Alabama"
"35031", "AL", " 34.111425", " -86.53380", "Blountsville", "Alabama"
"35033", "AL", " 33.952939", " -87.02889", "Bremen", "Alabama"
"35034", "AL", " 32.915182", " -87.21488", "Brent", "Alabama"
"35035", "AL", " 33.041166", " -86.95117", "Brierfield", "Alabama"
"35036", "AL", " 33.638150", " -86.91956", "Brookside", "Alabama"
"35040", "AL", " 33.107572", " -86.74996", "Calera", "Alabama"
"35042", "AL", " 32.963486", " -87.13867", "Centreville", "Alabama"
"35043", "AL", " 33.317093", " -86.66295", "Chelsea", "Alabama"
"35044", "AL", " 33.268471", " -86.35582", "Childersburg", "Alabama"
"35045", "AL", " 32.834501", " -86.64355", "Clanton", "Alabama"
"35046", "AL", " 32.894351", " -86.56504", "Clanton", "Alabama"

Here's the code I'll be working from

# prepare statement for use within insert loop
   sth = dbh.prepare("INSERT INTO people (id, name, height) VALUES(?, ?, ?)")

   # read each line from file, split into values, and insert into database
   File.open("people.txt", "r") do |f|
     f.each_line do |line|
       name, height = line.chomp.split("\t")
       sth.execute(nil, name, height)
     end
   end

Thanks guys.
Michael Litchard

2

Michael Litchard wrote:

Hi, I'm taking this flat file and putting it into a mysql table.
The question is, given the code I will be working from, what's the

Ruby way

to skip the first line?

One option:
irb(main):001:0> array = ["a","b"]
=> ["a", "b"]
irb(main):002:0> array.delete array.first
=> "a"
irb(main):003:0> array
=> ["b"]

Works if you, for example, read a file in lines.

And CSV/FasterCSV should have methods to skip a CSV header, too.

And there is the csv2sql gem, which could help, too.

- --
Phillip Gawlowski
Twitter: twitter.com/cynicalryan
Blog: http://justarubyist.blogspot.com

~ I imagine bugs and girls have a dim perception that nature played a
cruel trick on them, but they lack the intelligence to really comprehend
the magnitude of it. -- Calvin

3

Hi,

Hi, I'm taking this flat file and putting it into a mysql table.
The question is, given the code I will be working from, what's the Ruby way
to skip the first line?

Here's the code I'll be working from

# prepare statement for use within insert loop
  sth = dbh.prepare("INSERT INTO people (id, name, height) VALUES(?, ?, ?)")

  # read each line from file, split into values, and insert into database
  File.open("people.txt", "r") do |f|
    f.each_line do |line|

         next if f.lineno==1

      name, height = line.chomp.split("\t")
      sth.execute(nil, name, height)
    end
  end

Regards,

Park Heesob

···

2008/6/6 Michael Litchard <michael@schmong.org>:

4

# Here's the code I'll be working from
# <snip>

your code looks fine. don't change it just because of single header.
why not filter your data then pipe it to your code.

eg,

  sed 1d your_data | ruby your_code

kind regards -botp

···

From: Michael Litchard [mailto:michael@schmong.org]

5

Well, while waiting for a reply I decided to work out another problem. So,
in this example
imagine the top line has been manually removed.

Here's the code

#!/usr/pkg/bin/ruby18 -w
   # simple.rb - simple MySQL script using Ruby DBI module

   require "dbi"

   begin
     # connect to the MySQL server
     dbh = DBI.connect("DBI:mysql:test:localhost", "root", "")
     dbh.do("DROP TABLE IF EXISTS map")
     dbh.do("CREATE TABLE map (
                zip INT UNSIGNED,
                st_abv CHAR(2) NOT NULL,
                latitude FLOAT,
                longitude FLOAT,
                city CHAR(20),
                state CHAR(20),
                PRIMARY KEY (zip))")
     sth = dbh.prepare("INSERT INTO map
(zip,st_abv,latitude,longitude,city,stat
e) VALUES(?, ?, ?, ?, ?, ?)")

   # read each line from file, split into values, and insert into database
   File.open("zips.csv", "r") do |f|
     f.each_line do |line|
zip,st_abv,latitude,longitude,city,state = line.chomp.split(",")
       sth.execute(zip, st_abv, latitude, longitude, city, state)
     end
  end
     rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code: #{e.err}"
     puts "Error message: #{e.errstr}"
   ensure
     # disconnect from server
     dbh.disconnect if dbh
   end

More important than understanding what is happening here, is understanding
how to find out.
Troubleshooting methodology is what I'm after most of all.

Here's the error

# ./create_db.rb
An error occurred
Error code: 1062
Error message: Duplicate entry '0' for key 1

Michael Litchard

···

On Thu, Jun 5, 2008 at 6:40 PM, Michael Litchard <michael@schmong.org> wrote:

Hi, I'm taking this flat file and putting it into a mysql table.
The question is, given the code I will be working from, what's the Ruby way
to skip the first line?

Here's the table
"zip code", "state abbreviation", "latitude", "longitude", "city", "state"
"35004", "AL", " 33.606379", " -86.50249", "Moody", "Alabama"
"35005", "AL", " 33.592585", " -86.95969", "Adamsville", "Alabama"
"35006", "AL", " 33.451714", " -87.23957", "Adger", "Alabama"
"35007", "AL", " 33.232422", " -86.80871", "Alabaster", "Alabama"
"35010", "AL", " 32.903432", " -85.92669", "Alexander City", "Alabama"
"35014", "AL", " 33.355960", " -86.27720", "Alpine", "Alabama"
"35016", "AL", " 34.323715", " -86.49278", "Arab", "Alabama"
"35019", "AL", " 34.292540", " -86.63505", "Baileyton", "Alabama"
"35020", "AL", " 33.405559", " -86.95141", "Bessemer", "Alabama"
"35022", "AL", " 33.346817", " -86.95252", "Bessemer", "Alabama"
"35023", "AL", " 33.443039", " -87.01930", "Bessemer", "Alabama"
"35031", "AL", " 34.111425", " -86.53380", "Blountsville", "Alabama"
"35033", "AL", " 33.952939", " -87.02889", "Bremen", "Alabama"
"35034", "AL", " 32.915182", " -87.21488", "Brent", "Alabama"
"35035", "AL", " 33.041166", " -86.95117", "Brierfield", "Alabama"
"35036", "AL", " 33.638150", " -86.91956", "Brookside", "Alabama"
"35040", "AL", " 33.107572", " -86.74996", "Calera", "Alabama"
"35042", "AL", " 32.963486", " -87.13867", "Centreville", "Alabama"
"35043", "AL", " 33.317093", " -86.66295", "Chelsea", "Alabama"
"35044", "AL", " 33.268471", " -86.35582", "Childersburg", "Alabama"
"35045", "AL", " 32.834501", " -86.64355", "Clanton", "Alabama"
"35046", "AL", " 32.894351", " -86.56504", "Clanton", "Alabama"

Here's the code I'll be working from

# prepare statement for use within insert loop
  sth = dbh.prepare("INSERT INTO people (id, name, height) VALUES(?, ?,
?)")

  # read each line from file, split into values, and insert into database
  File.open("people.txt", "r") do |f|
    f.each_line do |line|
      name, height = line.chomp.split("\t")
      sth.execute(nil, name, height)
    end
  end

Thanks guys.
Michael Litchard

6

FasterCSV does:

   FCSV.foreach(…, :headers => true) do |row|
     …
   end

James Edward Gray II

···

On Jun 5, 2008, at 8:55 PM, Phillip Gawlowski wrote:

And CSV/FasterCSV should have methods to skip a CSV header, too.

7

# <snip>
# # read each line from file, split into values, and insert
# into database
# File.open("zips.csv", "r") do |f|
# f.each_line do |line|
# zip,st_abv,latitude,longitude,city,state = line.chomp.split(",")
# sth.execute(zip, st_abv, latitude, longitude, city, state)
# end
# end

···

From: Michael Litchard [mailto:michael@schmong.org]
#
# <snip>
# Here's the error
#
# # ./create_db.rb
# An error occurred
# Error code: 1062
# Error message: Duplicate entry '0' for key 1

that's no problem, since you're inserting records per line, just get the (csv) line that triggered the error.

btw, i do not see any conversion, is that automatic in mysql dbi?

kind regards -botp

8

There is no conversion, I figured out just now that I needed to do that. So
now I am figuring out the syntax of using the output of one function as the
input for another.
That's tomorrow's problem. I'll give it a go and see what happens.

···

On Thu, Jun 5, 2008 at 9:49 PM, Peña, Botp <botp@delmonte-phil.com> wrote:

From: Michael Litchard [mailto:michael@schmong.org]
# <snip>
# # read each line from file, split into values, and insert
# into database
# File.open("zips.csv", "r") do |f|
# f.each_line do |line|
# zip,st_abv,latitude,longitude,city,state = line.chomp.split(",")
# sth.execute(zip, st_abv, latitude, longitude, city, state)
# end
# end
#
# <snip>
# Here's the error
#
# # ./create_db.rb
# An error occurred
# Error code: 1062
# Error message: Duplicate entry '0' for key 1

that's no problem, since you're inserting records per line, just get the
(csv) line that triggered the error.

btw, i do not see any conversion, is that automatic in mysql dbi?

kind regards -botp