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'),
    )

Ben Hughes · Accepted Answer · 2009-07-10 14:59:54Z

3

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

answered Jul 10, 2009 at 14:59

Ben Hughes

14.2k1 gold badge43 silver badges34 bronze badges

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

2 Comments

jtorrance Over a year ago

Thanks very much! But how to add the single quotes? query="INSERT INTO animals(name, category) VALUES (#{values.join(",")})" gives INSERT INTO animals(name, category) VALUES ((snake, reptile),(frog, amphibian),(tuna, fish),(racoon, mammal))

Ben Hughes Over a year ago

use normal string interpolation

Codebeef · Accepted Answer · 2009-07-10 15:05:06Z

2

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(",")

edited Jul 10, 2009 at 15:05

answered Jul 10, 2009 at 14:57

Codebeef

44.1k23 gold badges90 silver badges119 bronze badges

Comments

twolfe18 · Accepted Answer · 2009-07-10 15:10:27Z

1

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"

answered Jul 10, 2009 at 15:10

twolfe18

2,2865 gold badges26 silver badges25 bronze badges

3 Comments

Ben Hughes Over a year ago

join is ruby magic? really? s = ""; def s.foo; puts "foo"; end; s.foo is magic. instance_eval{ define_method...}} is magic. const_missing is magic. join puts things together with another thing, which is kind of underwhelming when you think about it.

twolfe18 Over a year ago

meh, call me old school. i agree that it is in the fringe of "ruby magic", but ruby magic none the less (IMHO).

Codebeef Over a year ago

How is join any less 'magic' that length or << for that matter?

Mark · Accepted Answer · 2009-07-10 15:23:14Z

1

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)

edited Jul 10, 2009 at 15:23

answered Jul 10, 2009 at 15:01

Mark

17.3k9 gold badges46 silver badges55 bronze badges

Comments

jtorrance · Accepted Answer · 2009-07-11 03:49:29Z

0

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)

edited Jul 11, 2009 at 3:49

answered Jul 11, 2009 at 3:27

jtorrance

851 gold badge2 silver badges6 bronze badges

Collectives™ on Stack Overflow

Construct a valid MySQL query using Ruby/MySQL

5 Answers 5

2 Comments

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related