How can I iterate through a MySQL result set?

Question

Here is the code I'm using:

# Run the query against the database defined in .yml file.
# This is a Mysql::result object - http://www.tmtm.org/en/mysql/ruby/
@results = ActiveRecord::Base.connection.execute(@sql_query)

In my View, here's what I do to see the values:

<pre><%= debug @results %></pre>
Outputs: #<Mysql2::Result:0x007f31849a1fc0>

<% @results.each do |val| %>
   <%= val %>
<% end %>
Outputs: ["asdfasdf", 23, "qwefqwef"] ["sdfgdsf", 23, "asdfasdfasdf"]

So imagine I query something like select * from Person, and that returns a result set such as:

ID      Name      Age
1       Sergio    22
2       Lazlow    28
3       Zeus      47

How can I iterate through each value and output it?

The documentation here is not useful because I have tried methods that supposedly exist, but the interpreter gives me an error saying that those methods don't exist. Am I using the wrong documentation?

http://www.tmtm.org/en/mysql/ruby/

Thanks!

Josnidhin · Accepted Answer · 2012-05-31T15:56:25.507

If you are using mysql2 gem then you should be getting the mysql2 result object and according to the docs you should be able to do the following

results.each do |row|
  # conveniently, row is a hash
  # the keys are the fields, as you'd expect
  # the values are pre-built ruby primitives mapped from their corresponding field types in MySQL
  # Here's an otter: http://farm1.static.flickr.com/130/398077070_b8795d0ef3_b.jpg
end

Checkout the documentation here

So in you case you can do the following

<% @results.each do |val| %>
   <%= "#{val['id']}, #{val['name']}, #{val['age']}" %>
<% end %>

Edit: you seem to be referring to the wrong doc check the Mysql2 gems doc.

Thanks a bunch! I can't believe I was reading the wrong docs. :) — Only Bolivian Here, May 31 '12 at 16:00

score 16 · Answer 2 · answered May 24 '16 at 02:06

You could try using ActiveRecord::Base.connection.exec_query instead of ActiveRecord::Base.connection.execute which returns a ActiveRecord::Result (available in rails 3.1+)

Then you can access it in various ways like .rows, .each, or .to_hash

From the docs:

result = ActiveRecord::Base.connection.exec_query('SELECT id, title, body FROM posts')
result # => #<ActiveRecord::Result:0xdeadbeef>


# Get the column names of the result:
result.columns
# => ["id", "title", "body"]

# Get the record values of the result:
result.rows
# => [[1, "title_1", "body_1"],
      [2, "title_2", "body_2"],
      ...
     ]

# Get an array of hashes representing the result (column => value):
result.to_hash
# => [{"id" => 1, "title" => "title_1", "body" => "body_1"},
      {"id" => 2, "title" => "title_2", "body" => "body_2"},
      ...
     ]

# ActiveRecord::Result also includes Enumerable.
result.each do |row|
  puts row['title'] + " " + row['body']
end

score 5 · Answer 3 · answered Jan 07 '15 at 08:32

5

Use :as => :hash:

raw = ActiveRecord::Base.connection.execute(sql)
raw.each(:as => :hash) do |row|
  puts row.inspect # row is hash
end

answered Jan 07 '15 at 08:32

coderz

4,847
11
47
70

What is the benefit of using `(:as => :hash)` and not just `.each`? – Termato Jan 11 '16 at 15:48
1

@Termato In my laptop (Windows 7), Rails 3, it does not output `row` as hash, thus I explicitly add `(:as => :hash)`. Totally agree just use `.each` if it works:) – coderz Jan 11 '16 at 21:29
Thanks, that is very helpful. – Termato Jan 12 '16 at 14:40

score 2 · Answer 4 · answered Aug 30 '16 at 12:04

Look for @results.fields for column header.

Example: @results = [[1, "Sergio", 22],[2, "Lazlow", 28],[3, "Zeus", 47]]

@results.fields do |f|
  puts "#{f}\t"  # Column names
end

puts "\n"

@results.each do |rows| # Iterate through each row
  rows.each do |col| # Iterate through each column of the row
    puts "#{col}\t"
  end
  puts "\n"
end

Hope it is helpful.

How can I iterate through a MySQL result set?

4 Answers4

Linked