Database query

Hi all!
Im trying to learn ruby :slight_smile: 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 [email protected]
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 :slight_smile:

[1] http://datamapper.org

Panagiotis (atmosx) Atmatzidis

email: [email protected]
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… :slight_smile:

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

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 :slight_smile: I installed Datamapper by the way.

Br
cristian

Hello,

On 13 Δεκ 2012, at 17:56 , cristian cristian [email protected]
wrote:

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

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 :slight_smile:

 puts "Nothing"

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

Have a nice day!

Panagiotis (atmosx) Atmatzidis

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

Hello,

On 13 Δεκ 2012, at 19:39 , cristian cristian [email protected]
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 :slight_smile: I installed Datamapper by the way.

Your code is ‘correct’ syntactically but not ‘semantically’ :slight_smile:

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 :slight_smile:

Br
cristian


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

Panagiotis (atmosx) Atmatzidis

email: [email protected]
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! :slight_smile:

Now Im doing everything with Datamapper.

Br
cristian

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs