Victor Victor - 1 year ago 59
MySQL Question

Insert hash to MySQL via pure Ruby, database connection

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
con = 'localhost', 'root', '', 'data'

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

con.close if con


  1. @place
    is a hash. How can I quickly insert to the
    table other than iterating it?

  2. The loop will continue until the entire process ends. Should I close the connection after each insert, or leave it open until process ends?

  3. What if the process terminates unexpectedly? Will it affect the data if connection is not closed properly?

UPDATE: My first try:

col = { | key, value | key } # => ["name", "latitude", "longitude"]
result = { | 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

Answer Source

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']});"

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


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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download