# Newbie question concerning ruby and mysql

**URL:** <https://www.ruby-forum.com/t/newbie-question-concerning-ruby-and-mysql/139689>\
**Category:** Ruby\
**Created:** [June 6, 2008, 3:42am UTC](https://www.ruby-forum.com/t/newbie-question-concerning-ruby-and-mysql/139689 "2008-06-06T03:42:52Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![dreamwave](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7feea3/32.png) [@dreamwave](https://www.ruby-forum.com/u/dreamwave)\
**Post date:** [June 6, 2008, 3:42am UTC](https://www.ruby-forum.com/t/newbie-question-concerning-ruby-and-mysql/139689/1 "2008-06-06T03:42:52Z")

</div>

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 L.

---

<div class="post-metadata">

**Author:** ![dreamwave](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7feea3/32.png) [@dreamwave](https://www.ruby-forum.com/u/dreamwave)\
**Post date:** [June 6, 2008, 3:58am UTC](https://www.ruby-forum.com/t/newbie-question-concerning-ruby-and-mysql/139689/2 "2008-06-06T03:58:05Z")

</div>

-----BEGIN PGP SIGNED MESSAGE-----  
Hash: SHA1

Michael L. 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 G.  
Twitter: [twitter.com/cynicalryan](http://twitter.com/cynicalryan)  
Blog: [http://justarubyist.blogspot.com](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  
-----BEGIN PGP SIGNATURE-----  
Version: GnuPG v1.4.8 (MingW32)  
Comment: Using GnuPG with Mozilla - [http://enigmail.mozdev.org](http://enigmail.mozdev.org)

iEYEARECAAYFAkhImUoACgkQbtAgaoJTgL8huwCgieVUF7t2c2JpvAL0rCXzX49c  
lecAn26sUKKzdBzJEKfK3zZt3V1iKeRl  
=FQwq  
-----END PGP SIGNATURE-----

---

<div class="post-metadata">

**Author:** ![dreamwave](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7feea3/32.png) [@dreamwave](https://www.ruby-forum.com/u/dreamwave)\
**Post date:** [June 6, 2008, 4:10am UTC](https://www.ruby-forum.com/t/newbie-question-concerning-ruby-and-mysql/139689/3 "2008-06-06T04:10:22Z")

</div>

Hi,

2008/6/6 Michael L. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

> # 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 H.

---

<div class="post-metadata">

**Author:** ![dreamwave](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7feea3/32.png) [@dreamwave](https://www.ruby-forum.com/u/dreamwave)\
**Post date:** [June 6, 2008, 5:41am UTC](https://www.ruby-forum.com/t/newbie-question-concerning-ruby-and-mysql/139689/4 "2008-06-06T05:41:40Z")

</div>

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

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

FasterCSV does:

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

James Edward G. II

---

<div class="post-metadata">

**Author:** ![dreamwave](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7feea3/32.png) [@dreamwave](https://www.ruby-forum.com/u/dreamwave)\
**Post date:** [June 6, 2008, 4:22am UTC](https://www.ruby-forum.com/t/newbie-question-concerning-ruby-and-mysql/139689/5 "2008-06-06T04:22:40Z")

</div>

From: Michael L. [[mailto:removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)]

# Here’s the code I’ll be working from

# 

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

---

<div class="post-metadata">

**Author:** ![dreamwave](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7feea3/32.png) [@dreamwave](https://www.ruby-forum.com/u/dreamwave)\
**Post date:** [June 6, 2008, 6:01am UTC](https://www.ruby-forum.com/t/newbie-question-concerning-ruby-and-mysql/139689/6 "2008-06-06T06:01:34Z")

</div>

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 L.

On Thu, Jun 5, 2008 at 6:40 PM, Michael L. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![dreamwave](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7feea3/32.png) [@dreamwave](https://www.ruby-forum.com/u/dreamwave)\
**Post date:** [June 6, 2008, 7:14am UTC](https://www.ruby-forum.com/t/newbie-question-concerning-ruby-and-mysql/139689/7 "2008-06-06T07:14:14Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![dreamwave](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7feea3/32.png) [@dreamwave](https://www.ruby-forum.com/u/dreamwave)\
**Post date:** [June 6, 2008, 6:51am UTC](https://www.ruby-forum.com/t/newbie-question-concerning-ruby-and-mysql/139689/8 "2008-06-06T06:51:20Z")

</div>

From: Michael L. [[mailto:removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)]

# 

# # 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

# 

# 

# 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