Forum: Ruby Database query

Hi all!
Im trying to learn ruby :) and I have a problem when changing a hash to
a db table.

I made a hash with a key and value like this:

url = { "1" => "www.url1.com"
"2" => "www.url2.com"
....
}

Then I used it like this:

url.each do |number,url|
 if number == "1"
 ....
else
 ....
end

Now, I made a table in SQLite3 with four columns:
Key - integer
Category - Numeric
Description - Text
Url - Text

This is my little script and it works:

require 'sqlite3'
db = SQLite3::Database.new "test.db"
db.results_as_hash = true
db.execute( "SELECT Key,Url FROM Sites" ) do |site|
  puts "-> Site #%d %s" % [ site['Key'], site['Url'] ]
 end

Now my problem is that I dont know how to use this instead of my hash. I
thought that I could do something like this:

store_as_hash = db.execute( "SELECT Key,Url FROM Sites" )

and then just use this like I did with the hash above but it doesnt
work:

store_as_hash.each do |number,url|
 if number == "1"
 ....
else
 ....
end

I dont know if I can explain my problem in a better way....hopefully you
understand..How can I use the result from the query?

Br
cristian

cristian cristian wrote in post #1088899:
>
> and then just use this like I did with the hash above but it doesnt
> work:
>
> store_as_hash.each do |number,url|
>  if number == "1"
>  ....
> else
>  ....
> end
>

The result set is essentially an array of hashes(where each hash 
represents one row in the database), so you need to do something like 
this:

  db.results_as_hash = true
  result_set = db.execute( "SELECT Key,Url FROM Sites" )

  result_set.each do |row|
    row.each { |key, value| puts "#{key} => #{value}" }
    puts '*' * 20
  end


--output:--
Key => 1
Url => www.cool.com
0 => 1
1 => www.cool.com
********************
Key => 2
Url => www.bad.com
0 => 2
1 => www.bad.com
********************
Key => 3
Url => www.avg.com
0 => 3
1 => www.avg.com
********************


Note that results_as_hash lets you access the results by index value
too, so there are additional keys for the indexes.

Hello,

On 12 Δεκ 2012, at 21:48 , cristian cristian <lists@ruby-forum.com> 
wrote:

>
> Key - integer
>  puts "-> Site #%d %s" % [ site['Key'], site['Url'] ]
> store_as_hash.each do |number,url|
> cristian
>
> --
> Posted via http://www.ruby-forum.com/.
>



I would say that it's better to use datamapper[1] with ruby at this 
point. It can handle almost all basic functions needed by a database.
You can dive into raw SQLite3 later :-)

[1] http://datamapper.org

Panagiotis (atmosx) Atmatzidis

email:  atma@convalesco.org
URL:  http://www.convalesco.org
GnuPG ID: 0xE736C6A0
gpg --keyserver x-hkp://pgp.mit.edu --recv-keys 0xE736C6A0

Thank you for the help!
Panagiotis I will look into datamapper but now Im stuck with this
problem... :)

This always puts 'Nothing' even though I know I have a Key==7 in the
table. I miss something... Any suggestions?

result_set = db.execute( "SELECT Key,Url FROM Sites" )

result_set.each do |row|
  row.each do |key, value|
    if key=='7'
      puts "#{key} => #{value}"
    else
      puts "Nothing"
    end
    puts '*' * 20
  end
end


Br
cristian

Hello,


On 13 Δεκ 2012, at 17:56 , cristian cristian <lists@ruby-forum.com> 
wrote:

> Thank you for the help!
> Panagiotis I will look into datamapper but now Im stuck with this
> problem... :)

Datamapper will help you solve this problem (and other syntax problems) 
as it uses hashes widely and it's syntax is much more 'rubyish'.

However, the code 7stud guru posted is working perfectly. Take a look 
here
---------------------------------------------------------------------------------------------------------------
➜  archive git:(master) ✗ sqlite3 hellenic_words.db
SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
hellenic_words_databases
sqlite> select * from hellenic_words_databases;
1|καλημέρα|feea7f066965d3a9f1f19e67a4fcc5d81f313210|2012-12-11T15:27:27+01:00||||||
2|αλήθεια|8f01e8dbc7dc7c64ae80daa6558dcad679f48e3d|2012-12-11T15:27:27+01:00||||||
3|αγάπη|187adbb275e4e1df0caf32087f0dc0957fc1474b|2012-12-11T15:27:27+01:00||||||
4|Ελλάδα|d2e38adc17e42d109fdb946f564e70aa84f994d7|2012-12-11T15:27:27+01:00||||||
5|στροφή|0c3dbbf9303a42c0b678db7d59ff1c5aa53b8a12|2012-12-11T15:27:27+01:00||||||
6|αναποδιά|e22383f69bf6a033009057739a52bc16f87d4f2d|2012-12-11T15:27:27+01:00||||||
7|τιμόνι|b79aafd3f543ebefba9430d8e0d0033ed5072a89|2012-12-11T15:27:27+01:00||||||
8|αδελφή|24897aaa317b0dec6bb01d3abcbf17048cb8b6e3|2012-12-11T15:27:27+01:00||||||
9|ποταμός|95ae1fed2b86c551911e78f107f38b777c777d9a|2012-12-11T15:27:27+01:00||||||
10|κανάτα|cf951867e94cf8474e5ccd700966b2c603d9ebf4|2012-12-11T15:27:27+01:00||||||
11|φασολάδα|e5ea942c97d8c0f2c9a37cd0f1a271beeedb5a98|2012-12-11T15:27:27+01:00||||||
12|παιχνίδι|d752a274657538e752e6eeedbd34a1359ac11d8d|2012-12-11T15:27:27+01:00||||||
13|παπούτσι|2eaea11ab23d6ac0a7497f978887f4f6330c1fce|2012-12-11T15:27:27+01:00||||||
14|παντόφλα|e68e9ad399ddce4572fd7a7ed33962dda842b231|2012-12-11T15:27:27+01:00||||||
15|κομοδίνο|dafd4a50b1ff983cd1cfff7160320c64af3ee7fe|2012-12-11T15:27:27+01:00||||||
16|δίσκος|5a3a80cc90756999bab5b81775311be2e096e873|2012-12-11T15:27:27+01:00||||||
17|κουτί|ff7f9d9b16a51770a31863f9296cb903af97d0e9|2012-12-11T15:27:27+01:00||||||
sqlite> .quit
➜  archive git:(master) ✗ ruby sample.rb
word => καλημέρα
sha1 => feea7f066965d3a9f1f19e67a4fcc5d81f313210
0 => καλημέρα
1 => feea7f066965d3a9f1f19e67a4fcc5d81f313210
********************
word => αλήθεια
sha1 => 8f01e8dbc7dc7c64ae80daa6558dcad679f48e3d
0 => αλήθεια
1 => 8f01e8dbc7dc7c64ae80daa6558dcad679f48e3d
********************
word => αγάπη
sha1 => 187adbb275e4e1df0caf32087f0dc0957fc1474b
0 => αγάπη
1 => 187adbb275e4e1df0caf32087f0dc0957fc1474b
********************
[...]
➜  archive git:(master) ✗ cat sample.rb
#!/usr/bin/env ruby
# encoding: UTF-8

require 'sqlite3'
db = SQLite3::Database.open('hellenic_words.db')

db.results_as_hash = true
result_set = db.execute( "SELECT word,sha1 FROM 
hellenic_words_databases" )

result_set.each do |row|
  row.each { |key, value| puts "#{key} => #{value}" }
  puts '*' * 20
end
---------------------------------------------------------------------------------------------------------------

So you're obviously doing something wrong :-)

>      puts "Nothing"
> Posted via http://www.ruby-forum.com/.
>

Have a nice day!


Panagiotis (atmosx) Atmatzidis

email:  atma@convalesco.org
URL:  http://www.convalesco.org
GnuPG ID: 0xE736C6A0
gpg --keyserver x-hkp://pgp.mit.edu --recv-keys 0xE736C6A0

Hi!
Yes! 7Studs code works perfectly. But I changed the code to work as I 
want it and that doesnt work (see my prev comment) probably because I 
dont know what Im doing :) I installed Datamapper by the way.

Br
cristian

Hello,

On 13 Δεκ 2012, at 19:39 , cristian cristian <lists@ruby-forum.com> 
wrote:

> Hi!
> Yes! 7Studs code works perfectly. But I changed the code to work as I
> want it and that doesnt work (see my prev comment) probably because I
> dont know what Im doing :) I installed Datamapper by the way.

Your code is 'correct' syntactically but not 'semantically' :-)

Take a look here: https://gist.github.com/4278643

Works fine. the problem is that your value when it comes out of the 
database is not a 'string' while your '7' is a string... So you're 
matching a number(?) with a string which
will never work :-)

>
> Br
> cristian
>
> --
> Posted via http://www.ruby-forum.com/.
>


Panagiotis (atmosx) Atmatzidis

email:  atma@convalesco.org
URL:  http://www.convalesco.org
GnuPG ID: 0xE736C6A0
gpg --keyserver x-hkp://pgp.mit.edu --recv-keys 0xE736C6A0

...ok..
key is an integer. How can I compare the integer with a number?

Br
cristian

cristian cristian wrote in post #1089016:
> ...ok..
> key is an integer. How can I compare the integer with a number?
>
> Br
> cristian

The keys are the names of the columns in your database.  That is what
setting db.results_as_hash does.   The names of your columns are "Key",
"Category", "Description", and "Url", so none of your column names is
ever going to be equal to 7 or "7".  When you write:

  row.each do |key, value|

...that creates a loop that steps through the row. Every
time through the loop ruby assigns one column name to the variable key,
and the corresponding entry for that column is assigned to the variable
value.

If the variable names in the loop are confusing, then change them:

  row.each do |column_name, value|
    if column_name == "Key" and value == 7
      #do stuff
    end
  end

Or, you can just write this:

  result_set.each do |row|
    value = row["Key"]

    if value == 7
      puts "Key: #{value}"
      puts '*' * 20
    end

  end

OMG! It works!
Thank you! :)


Now Im doing everything with Datamapper.

Br
cristian