Victor Victor - 22 days ago 6
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
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


  1. @place
    is a hash. How can I quickly insert to the
    data
    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 = @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

Answer

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.