Retrieving array from MySQL

Question

I'm not using sqlite3 gem
I'm using mysql2 gem

I'm retrieving data from MySQL database given that it meets the condition of a certain event type and severity. However, it returns only one row instead of an array of results. It really puzzles me. Shouldnt .map return an array?

   result = connect.query("SELECT * FROM data WHERE event_type = 'ALARM_OPENED' AND severity = '2'") 
    equipments = result.map do |record| 
        [
            record['sourcetime'].strftime('%H:%M:%S'), 
            record['equipment_id'], 
            record['description']
        ]
    end
    p equipments

Possible duplicate of Return Values of #execute and #query methods of sqlite3 gem — JollyProgrammer
– JollyProgrammer, Commented Mar 23, 2018 at 6:38
You are using DBI right? The interface should be the same for both databases. — JollyProgrammer
– JollyProgrammer, Commented Mar 23, 2018 at 9:09
@JollyProgrammer I'm not sure what DBI is, but one thing for sure is that I dont use $db in my code. I'm just querying MySQL using Ruby. — Joshua Ng
– Joshua Ng, Commented Mar 23, 2018 at 9:22
what is the connect object in your code? DBI stands for Database Independent Interface for Ruby. — JollyProgrammer
– JollyProgrammer, Commented Mar 23, 2018 at 9:43

JollyProgrammer · Accepted Answer · 2018-04-03 12:06:11Z

1

I had misread your question...I think what you are looking for is in here.

UPDATE

You can use each instead, like this:

#!/usr/bin/env ruby
require 'mysql2'

connect= Mysql2::Client.new(:host => '', :username => '', :password => '', :database => '')

equipments = []
result = connect.query("SELECT * FROM data WHERE event_type = 'ALARM_OPENED' AND severity = '2'", :symbolize_keys => true).each do |row|
    equipments << [ 
                    row[:sourcetime].strftime('%H:%M:%S'), 
                    row[:equipment_id], 
                    row[:description] 
                  ]
end

puts "#equipments {equipments}"

EDITED:

I forgot to add .each at the end of the query. So it was returning the initialized empty array instead.

edited Apr 3, 2018 at 12:06

answered Mar 22, 2018 at 13:32

JollyProgrammer

1867 bronze badges

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

9 Comments

Joshua Ng Over a year ago

pluck is not Ruby Core, what do you require ?

Yunnosch Over a year ago

Consider replacing this by a duplicate note.

JollyProgrammer Over a year ago

How do I do that? I am new here :)

Joshua Ng Over a year ago

@Yunnosch What do you mean by that?

Yunnosch Over a year ago

@JoshuaNg At the time of my comment, the answer just read "Look in this other answer", with a link. That kind of answer is unneeded and should be replaced by a flag as duplicate. The answer has changed now and seems now to contain more than just the link, which I assume is different from the linked answer. This answer can now be evaluated by its own content.

|

Krishna Gupta · Accepted Answer · 2018-03-22 10:10:51Z

0

You must need to change your sql statement :

result = connect.query("SELECT * FROM data WHERE event_type = 'ALARM_OPENED' AND severity = '2'", :as => :array)

answered Mar 22, 2018 at 10:10

Krishna Gupta

6854 silver badges15 bronze badges

3 Comments

Joshua Ng Over a year ago

I get an error saying no implicit conversion of String into Integer. I tried adding .to_s but the error still persists

JollyProgrammer Over a year ago

The problem with :as => :array is that it will return each row as an array. So when you try to get the values like record['equipment_id'] it tries to convert 'equipment_id' to an integer and crashes. Can you try to run the original query that you had with this line after: result.count?

Joshua Ng Over a year ago

@JollyProgrammer The count is 1

Collectives™ on Stack Overflow

Retrieving array from MySQL

2 Answers 2

9 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related