How to obtain result as array of Hashes in Ruby (mysql2 gem)

Question

I'm using Ruby's 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 the above .query call to return array of hashes, each hesh in the res array to be in the format column => value. I can do this manually but from the docs I was left with the impression that I can get the results directly loaded in memory in the mentioned format. I need this, because after that I have to encode the result in json anyway, so there is no advantage for me to fetch the rows one by one. Also the amount of data is always very small.

Answer 1

Change

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

to:

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

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

Answer 2

You can always fetch the results as JSON directly:

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

The default format, as far as I know, is an array of hashes. If you want symbol keys you need to ask for those. A lot of this is documented in the gem itself.

You should also be extremely cautious about inserting things into your query with string substitution. Whenever possible, use placeholders. These aren't supported by the mysql2 driver directly, so you should use an adapter layer like ActiveRecord or Sequel.