Construct a valid MySQL query using Ruby/MySQL

Question

If using the Ruby/MSQL library I returned an object of type Mysql::Result, how would I construct a string that loops through the object and constructs a valid MySQL query? I am having trouble figuring out how to drop the final comma from the output below, and how to pass this output back to the query() method.

require 'mysql'
dbh = Mysql.real_connect("host", "user", "pass", "db")
res = dbh.query("SELECT name, category FROM animals")
printf "INSERT INTO animals (name, category) VALUES (\n"
res.each_hash do |row|
printf "('%s', '%s'),\n", row["name"], row["category"]
end
printf ")\n"

#Output    
INSERT INTO animals (name, category) VALUES (
    ('snake', 'reptile'),
    ('frog', 'amphibian'),
    ('tuna', 'fish'),
    ('raccoon', 'mammal'),
    )

Answer 1

I'd use join in this case, so rather than looping, and adding a comma each time, do something like:

["One", "Two", "Three"].join(',')

You'll need to turn your hash into an array containing the elements you are interested in first, of course! Try this:

(res.each_hash{|row| "(#{row["name"]}, #{row["category"]})"}).join(",")

Answer 2

values = []
res.each_row do |r|
  values << "(#{r.map{|x| dbh.quote(x) }.join(", ")})"
end
query =<-SQL
  INSERT INTO animals(name, category) VALUES
    (#{values.join(",\n")}
    )
SQL

puts query

Answer 3

One way to do this would be to:

    require 'mysql'    
    dbh = Mysql.real_connect("host", "user", "pass", "db")    
    res = dbh.query("SELECT name, category FROM animals")   
    stmt = "INSERT INTO animals (name, category) VALUES ("    
    res.each_hash do |row|
        stmt << "(#{row["name"]}, #{row["category"]})" 
    end    
    stmt << ")"

"how to pass this output back to the query() method"

     dbh.query(stmt)

Answer 4

in general, if you don't want to just any Ruby magic (like .join() ), you can just loop over the array/hash by index and only add the comma if it is not the last element:

  if(index < array.length) string << ",\n"

Answer 5

Thanks for all the suggestions. Using Ben Hughes' example, here is the 'hashed' version:

values = []
res.each_hash do
  |row| values << "('#{row.map{|d,x| dbh.quote(x) }.join("', '")}')" 
  end
puts values.join(",")

query="INSERT INTO animals (name, category) VALUES #{values.join(", ")}"
dbh.query(query)