Database query

ruby-talk
1

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

···

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

2

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

  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.

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

3

Hello,

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

--
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: atma@convalesco.org
URL: http://www.convalesco.org
GnuPG ID: 0xE736C6A0
gpg --keyserver x-hkp://pgp.mit.edu --recv-keys 0xE736C6A0

···

On 12 Δεκ 2012, at 21:48 , cristian cristian <lists@ruby-forum.com> wrote:
--
The wise man said: "Never argue with an idiot. They bring you down to their level and beat you with experience."

4

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

···

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

5

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

···

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

6

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

Br
cristian

···

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

7

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

  row.each do |column_name, value|

···

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

8

OMG! It works!
Thank you! :slight_smile:

Now Im doing everything with Datamapper.

Br
cristian

···

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

9

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... :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 ";"

.tables

hellenic_words_databases

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

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

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

--
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
--
The wise man said: "Never argue with an idiot. They bring you down to their level and beat you with experience."

10

Hello,

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: atma@convalesco.org
URL: http://www.convalesco.org
GnuPG ID: 0xE736C6A0
gpg --keyserver x-hkp://pgp.mit.edu --recv-keys 0xE736C6A0

···

On 13 Δεκ 2012, at 19:39 , cristian cristian <lists@ruby-forum.com> wrote:
--
The wise man said: "Never argue with an idiot. They bring you down to their level and beat you with experience."

11

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.

···

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