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-31 15:56:25Z

34

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.

edited May 31, 2012 at 15:56

answered May 31, 2012 at 15:47

Josnidhin

12.5k9 gold badges45 silver badges62 bronze badges

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

1 Comment

Only Bolivian Here Over a year ago

Thanks a bunch! I can't believe I was reading the wrong docs. :)

hajpoj · Accepted Answer · 2016-05-24 02:06:44Z

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

coderz · Accepted Answer · 2015-01-07 08:32:31Z

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 7, 2015 at 8:32

coderz

5,01912 gold badges53 silver badges80 bronze badges

2 Comments

Termato Over a year ago

What is the benefit of using (:as => :hash) and not just .each?

coderz Over a year ago

@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:)

Arun Jain · Accepted Answer · 2016-08-30 12:04:12Z

2

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.

answered Aug 30, 2016 at 12:04

Arun Jain

613 bronze badges

Collectives™ on Stack Overflow

How can I iterate through a MySQL result set?

4 Answers 4

1 Comment

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related