Insert hash to MySQL via pure Ruby, database connection

Question

I'm using mysql gem and Ruby 1.9.3, not using Rails. I have the following:

#!/bin/env ruby
# encoding: utf-8

require 'rubygems'
require 'mysql'

# Open DB connection
begin
  con = Mysql.new 'localhost', 'root', '', 'data'

  con.query("CREATE TABLE IF NOT EXISTS
    shops(id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(255),
      latitude DECIMAL(15,10),
      longitude DECIMAL(15,10)
    )")

  ### Loop Starts ### 
    ...

    @place = {"name"=>"Tuba", "latitude"=>13.7383, "longitude"=>100.5883}
    # Write to DB

  ### Loop Ends ###

rescue Mysql::Error => e
  puts e.errno
  puts e.error

ensure
  con.close if con
end

Questions

@place is a hash. How can I quickly insert to the data table other than iterating it?
The loop will continue until the entire process ends. Should I close the connection after each insert, or leave it open until process ends?
What if the process terminates unexpectedly? Will it affect the data if connection is not closed properly?

UPDATE: My first try:

col = @place.map { | key, value | key } # => ["name", "latitude", "longitude"]
result = @place.map { | key, value | value } # => ["Tuba", 13.7383, 100.5883]
con.query("INSERT INTO shops (#{col}) VALUES(#{result});")

This, as expected, generates the following error:

You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use 
near '["name", "latitude", "longitude"] at line 1

I have one question, to install MysqlRuby all I need to do is gem install mysql right? If anything more need,could you help me? — Arup Rakshit
– Arup Rakshit, Commented Sep 20, 2013 at 19:18
@ArupRakshit, are you using pure Ruby or Rails? If pure ruby, yes, you just need to run gem install mysql, then add the 2 require lines in your Ruby file; if Rails, you put gem mysql2 in your Gemfile. — Victor
– Victor, Commented Sep 20, 2013 at 19:20
I am using only Ruby..only. I just saw this link dev.mysql.com/downloads/ruby.html ... I am on Ruby2.0-p0,so I don't need require 'rubygems'.. :) I think so.. Do I need to install MySQL server seperately ? — Arup Rakshit
– Arup Rakshit, Commented Sep 20, 2013 at 19:25
@ArupRakshit Of course you need to install MySQL on your computer. — Victor
– Victor, Commented Sep 20, 2013 at 19:29
can you join here? chat.stackoverflow.com/rooms/37753/mysql-ruby-installation — Arup Rakshit
– Arup Rakshit, Commented Sep 20, 2013 at 19:31

xorpaul · Accepted Answer · 2016-11-18 17:26:03Z

4

I would make a method for inserting your data:

def insert_place(hash, con)
   statement = "INSERT INTO shops (name, latitude, longitude) VALUES (#{hash['name']}, #{hash['latitude']}, #{hash['longitude']});"
   con.query(statement)
end

This method takes your hash and the connection object as parameters. You should reuse your connection whenever possible.

Then in your loop I would use this method like this:

@place = {"name"=>"Tuba", "latitude"=>13.7383, "longitude"=>100.5883}
insert_place(@place, con)

And lastly to answer your last question... if the program terminates in the middle of your loop, I don't see anything that would "corrupt" your data because its a single query and it will either succeed or fail.. nothing in between. If you want to be able to run your script again in the event of a failure you would need to identify where you left off because running again will cause duplicates.

You could do this manually and curate your data appropriately

OR

you could just add it to your insert_place method so that it will skip the con.query(statement) bit if the entry is already in the database.

edited Nov 18, 2016 at 17:26

xorpaul

2073 silver badges10 bronze badges

answered Sep 20, 2013 at 19:19

Mike Heijmans

4394 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Heijmans Over a year ago

if you are using the raw mysql gem.. yes. its just a wrapper around the mysql c client library. The other option is to use Activerecord. It will abstract all that for you.

gotva · Accepted Answer · 2013-09-20 19:58:52Z

-1

It seems to me that you need to extract values from array

con.query("INSERT INTO shops (#{*col}) VALUES(#{*result});")

Some improvements in code can be done. I hope this will work

col = @place.keys # => ["name", "latitude", "longitude"]
result = @place.values # => ["Tuba", 13.7383, 100.5883]
con.query("INSERT INTO shops (#{*col}) VALUES(#{*result});")
# (not tested variant)
con.query("INSERT INTO shops (?) VALUES(?);", col, result)

answered Sep 20, 2013 at 19:58

gotva

6,0082 gold badges29 silver badges36 bronze badges

1 Comment

Ev Dolzhenko Over a year ago

"INSERT INTO shops (#{*col}) VALUES(#{*result});" is a syntax error.

Collectives™ on Stack Overflow

Insert hash to MySQL via pure Ruby, database connection

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related