Help with database-related code pls

ruby-talk
1

Hi guys! This is just a part of the code of a program that can load a
database file, add a table in it, and find an entry (a person).

def find_person

  puts "Enter name or ID of person to find:"
  idorname = gets.chomp #i would pronounce the variable as "ID or
Name"

  person = $db.execute("SELECT * FROM people WHERE name = ? OR id = ?",
           idorname, idorname.to_i).first

  unless person
    puts "No result found"
    return
  end
  puts %Q{Name: #{person['name']}
    Job: #{person['job']}
    Gender: #{person['gender']}
    Age: #{person['age']}}

end

I understand most of it except the fourth line. Notice the ".first" at
the end of the $db.execute code.

Can anyone explain its significance please. Thanks so much!

···

--
Posted via http://www.ruby-forum.com/.

2

Hi,

Calling $db.execute retrieves all rows for this query. In order to get
only the first one, you call first.

···

--
Posted via http://www.ruby-forum.com/.

3

Kaye Ng wrote in post #1071689:

  person = $db.execute("SELECT * FROM people WHERE name = ? OR id = ?",
           idorname, idorname.to_i).first

Try adding after this line:

p person

or

puts person.inspect

Run the code. Then run it again without the ".first" and see what the
difference is.

Inspecting variables at various points in your program is a very simple
but powerful way to help you understand what's going on.

···

--
Posted via http://www.ruby-forum.com/\.

4

Why do you guys keep saying that it's an array when you have no idea
which database library this even is?

It's most likely *not* array but some kind of "Result" object (like
Mysql::Result or PG::Result for PosgreSQL). And getting a "strange"
object when everybody is telling you that you should get a normal array
will be very confusing for a beginner.

···

--
Posted via http://www.ruby-forum.com/.

5

Try adding after this line:

p person

or

puts person.inspect

Run the code. Then run it again without the ".first" and see what the
difference is.

Hello. Ahead of you on that one, but I still don't understand. What can
I say, I'm stupid.

"puts person.inspect" returns:
{"id"=>3, "name"=>"Dave Ellefson", "job"=>"Bassist", "gender"=>"Male",
"age"=>47, 0=>3, 1=>"Dave Ellefson", 2=>"Bassist", 3=>"Male", 4=>47}

I understand why it's in the form of a hash (because of
$db.results_as_hash = true). What I don't understand is why the
repeated values? Take note of the number of elements, I was expecting 5
only (id, name, job, gender, and age), but it has 10 because the values
(with 0,1,2,3,4 keys) were repeated.

Another thing I don't understand is that when I remove the ".first", run
the program, enter a valid id or name, or just about anything really, it
results in:

F:/Ruby/Practice/database3.rb:34:in `': can't convert String into
Integer (TypeError)
        from F:/Ruby/Practice/database3.rb:34:in `find_person'
        from F:/Ruby/Practice/database3.rb:70:in `block in <main>'
        from F:/Ruby/Practice/database3.rb:57:in `loop'
        from F:/Ruby/Practice/database3.rb:57:in `<main>'

The 34th line is:
puts %Q{Name: #{person['name']}

Help pls? Thanks so much!

···

--
Posted via http://www.ruby-forum.com/\.

6

Jan E. wrote in post #1072075:

Why do you guys keep saying that it's an array when you have no idea
which database library this even is?

Sure, it might not actually be Array, but it's clearly some sort of
collection which wraps the result rows.

I'm only guessing it's Array just because the rows seem to be plain
Hashes rather than any fancy ORM wrapper object.

Let the OP print result.class and then we'll know for sure.

···

--
Posted via http://www.ruby-forum.com/\.

7

I understand why it's in the form of a hash (because of
$db.results_as_hash = true). What I don't understand is why the
repeated values? Take note of the number of elements, I was expecting 5
only (id, name, job, gender, and age), but it has 10 because the values
(with 0,1,2,3,4 keys) were repeated.

That's just how some database adapters work, so you can access the
results both by columns name (person['name']) and by its number
(person[1]).

Another thing I don't understand is that when I remove the ".first", run
the program, enter a valid id or name, or just about anything really, it
results in:

Because .execute returns an array of hashes (even if this array only
has one item). .first the chooses only the first one of them.

-- Matma Rex

···

2012/8/9 Kaye Ng <lists@ruby-forum.com>:

8

Brian Candler wrote in post #1072080:

Jan E. wrote in post #1072075:

Why do you guys keep saying that it's an array when you have no idea
which database library this even is?

Sure, it might not actually be Array, but it's clearly some sort of
collection which wraps the result rows.

I'm only guessing it's Array just because the rows seem to be plain
Hashes rather than any fancy ORM wrapper object.

Let the OP print result.class and then we'll know for sure.

I think it is not really an "Array" per se, but something that includes
the Enumerable module (just like in sqlite3 or mysql2).

Regards,

Bill

···

--
Posted via http://www.ruby-forum.com/\.

9

Another thing I don't understand is that when I remove the ".first", run
the program, enter a valid id or name, or just about anything really, it
results in:

Because .execute returns an array of hashes (even if this array only
has one item). .first the chooses only the first one of them.

-- Matma Rex

Thank you Rex, but how is it an array of hashes?

'puts person.inspect' returns:

{"id"=>3, "name"=>"Dave Ellefson", "job"=>"Bassist", "gender"=>"Male",
"age"=>47, 0=>3, 1=>"Dave Ellefson", 2=>"Bassist", 3=>"Male", 4=>47}

Isn't {key => value, key => value, etc. etc} just a Hash? (not an array
of hashes)

···

--
Posted via http://www.ruby-forum.com/\.

10

Kaye Ng wrote in post #1071965:

Thank you Rex, but how is it an array of hashes?

'puts person.inspect' returns:

{"id"=>3, "name"=>"Dave Ellefson", "job"=>"Bassist", "gender"=>"Male",
"age"=>47, 0=>3, 1=>"Dave Ellefson", 2=>"Bassist", 3=>"Male", 4=>47}

Isn't {key => value, key => value, etc. etc} just a Hash? (not an array
of hashes)

Yes it is. And that was to be expected, because "person" is the result
of the "first" method. So it's the first row of the table.

I guess this was a misunderstanding, and they actually wanted you to
inspect the query result (without the "first"). However, this probably
won't reveal anything useful. I'm pretty sure it's not an actual array
of hashes but rather some kind of result object (which only behaves like
an array).

Anyway, do you understand now why there's a "first"? Calling "execute"
returns some kind of enumeration object for the rows of the query
result. And if you want to get a specific row, you have to fetch it from
this object. You can also call "each" on the result to loop through the
rows:

result = $db.execute("SELECT * FROM people WHERE name = ? OR id = ?",
           idorname, idorname.to_i)
result.each do |row|
  p row
end

This should display all rows one after another.

···

--
Posted via http://www.ruby-forum.com/\.

11

Try rewriting the method like this to understand what is going on:

def find_person

  puts "Enter name or ID of person to find:"
  idorname = gets.chomp #i would pronounce the variable as "ID or Name"

  # Get an array of people hashes that match the query conditions
  all_the_people = $db.execute("SELECT * FROM people WHERE name = ? OR id =
?", idorname, idorname.to_i)

  # Grab the first hash from the array. If the array is empty, #first will
return nil
  person = all_the_people.first

  unless person
    puts "No result found"
    return
  end
  puts %Q{Name: #{person['name']}
    Job: #{person['job']}
    Gender: #{person['gender']}
    Age: #{person['age']}}

end

12

Kaye Ng wrote in post #1071965:

Another thing I don't understand is that when I remove the ".first", run
the program, enter a valid id or name, or just about anything really, it
results in:

Because .execute returns an array of hashes (even if this array only
has one item). .first the chooses only the first one of them.

-- Matma Rex

Thank you Rex, but how is it an array of hashes?

'puts person.inspect' returns:

{"id"=>3, "name"=>"Dave Ellefson", "job"=>"Bassist", "gender"=>"Male",
"age"=>47, 0=>3, 1=>"Dave Ellefson", 2=>"Bassist", 3=>"Male", 4=>47}

Isn't {key => value, key => value, etc. etc} just a Hash? (not an array
of hashes)

If you remove the .first, you should see

[{"id"=>3, "name"=>"Dave Ellefson", ...}]

The outer [...] shows that you have an Array, containing one element.

Another thing to try is looking at the class of the object. e.g.

  result = $db.execute("SELECT * FROM people WHERE name = ? OR id = ?",
           idorname, idorname.to_i) # without .first
  puts result.class, result.inspect
  person = result.first
  puts person.class, person.inspect

···

--
Posted via http://www.ruby-forum.com/\.