为什么Rails 3使用Mysql2 Gem ActiveRecord::Base.connection.execute(sql)返回数组而不是散列?

I'm in the process of upgrading an application to Rails 3. I've decided to go with the mysql2 gem. There's some legacy code in the app that makes calls like:

我正在将一个应用程序升级到Rails 3。我决定使用mysql2 gem。在这个应用程序中有一些遗留代码可以调用:

results = ActiveRecord::Base.connection.execute(sql)

In the 2.3.x version, it used

在2.3。x版本,它使用

results.each_hash do |row|
...

But with gem mysql2, results is type Mysql2::Result, which has only an each method. Checked the docs and they specify results should be a hash keyed on field name. Great!

但是使用gem mysql2，结果是类型mysql2::结果，它只有一个方法。检查文档并指定结果应该是一个字段名的散列。太棒了!

But in fact, it is an Array, not a Hash.

但实际上，它是一个数组，而不是散列。

When I use the rails console and instantiate my own Mysql2::Client and run the query there, the results are a Hash, which is what I want.

当我使用rails控制台并实例化我自己的Mysql2::客户机并在那里运行查询时，结果是一个散列，这是我想要的。

In the rails application, I think it's better to use ActiveRecord::Base.connection, since it's been instantiated with options from database.yml.

在rails应用程序中，我认为最好使用ActiveRecord::Base。连接，因为它是通过database.yml的选项实例化的。

Note, unfortunately the result does not map to a model, so I can't use that.

注意，不幸的是结果没有映射到模型，所以我不能使用它。

What I've done for now is, for example:

我现在所做的就是，例如:

result = ActiveRecord::Base.connection.execute(sql)
field_index = result.fields.index("field")
result.each do |row|
  row[field_index]
end

Which is ugly as sin.

这是丑陋的罪恶。

Does anyone how I can get it to return a Hash instead of Array?

有没有人知道如何返回一个哈希而不是数组?

5 个解决方案

#1

If you just want to reuse the database.yml configuration, you can do this:

如果您只想重用数据库。yml配置，你可以这样做:

config = ActiveRecord::Base.configurations[RAILS_ENV].symbolize_keys
conn = Mysql2::Client.new(config)
conn.query("select * from users").each do |user|
  # user should be a hash
end

#2

I faced a similar issue a while back and found this to work:

我曾遇到过类似的问题，但我发现这是可行的:

result = ActiveRecord::Base.connection.execute(sql) 
result.each(:as => :hash) do |row| 
   row["field"] 
end

edit: you could also use the select_all method of the connection object that returns a hash

编辑:您还可以使用返回散列的连接对象的select_all方法。

#3

instead of

而不是

result = ActiveRecord::Base.connection.execute(sql)

do

做

results = ActiveRecord::Base.connection.exec_query(sql)

And that'll do exactly what you want. In particular,

这就是你想要的。特别是,

results.first

will be a hash, and so on.

将会是一个散列，等等。

Thanks to @_fx for figuring this out!

感谢@_fx解决这个问题!

For more, see http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/Mysql2Adapter.html#method-i-exec_query

更多信息,见http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/Mysql2Adapter.html # method-i-exec_query

#4

results = ActiveRecord::Base.connection.select(sql) 

table header

results.first.keys.each do |key|
 key
end

table data

results.each do |result| %>
  result.values.each do |value| %>
    value
  end
end

#5

Improving dan's answer, Rails 3.2.8 won't accept RAILS_ENV.

改进dan的答案，Rails 3.2.8不会接受RAILS_ENV。

    config = ActiveRecord::Base.configurations[Rails.env].symbolize_keys
    conn = Mysql2::Client.new(config)
    conn.query("select * from users").each do |user|
        # user should be a hash
    end