CODEWITHSUNDEEP
mysqlruby
Victor
Victor

Reputation: 13388

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

Upvotes: 1

Views: 4644

Answers (2)

Mike Heijmans
Mike Heijmans

Reputation: 439

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.

Upvotes: 4

gotva
gotva

Reputation: 5998

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)

Upvotes: -1

Related Questions