How to get the result as an array of hashes in Ruby (mysql2 gem)

Question

I am using the Ruby mysql2 gem found here: https://github.com/brianmario/mysql2

I have the following code:

client = Mysql2::Client.new( :host => dbhost, :port => dbport, :database => dbname, :username => dbuser, :password => dbpass) sql = "SELECT column1, column2, column3 FROM table WHERE id=#{id}" res = client.query(sql, :as => :array) p res # prints #<Mysql2::Result:0x007fa8e514b7d0> 

Is it possible that the above .query call returns an hash array, each hash in the res array should be in the format column => value . I can do this manually, but from the documents I have the impression that I can get the results directly loaded into memory in the specified format. I need this because after that I still need to code the result in json, so for me there is no advantage to getting strings one by one. Also, the amount of data is always very small.

Answer 1

Edit

 res = client.query(sql, :as => :array) 

in

 res = client.query(sql, :as => :hash) 

As @Tadman says,: :as => :hash is the default, so you really don't need to specify anything.

Answer 2

You can always get the results directly in JSON:

 res = client.query(sql, :as => :json) 

The default format, as far as I know, is an array of hashes. If you need character keys, you need to ask for them. Many of them are documented in the stone itself.

You also need to be very careful about inserting things into your query with string replacement. Use placeholders whenever possible. They are not supported directly by the mysql2 driver, so you should use an adapter layer, such as ActiveRecord, or Sequel .

Answer 3

the source code for mysql2 is implemented by MySql2 :: The result is just include Enumerable , so the obvious way to access the data is to use any method implemented in the Enumerable doc here .

For example, #each, #each_with_index, #collect, and #to_a are all useful ways to access Result elements.

 puts res.collect{ |row| "Then the next result was #{row}" }.join("\t")