Beginners problem with database and datamapper

[7stud2 (7stud --)]

Hi all!

I'm a complete beginner at this so please bear with me...I dont program
at all but if I need to then I have been trying to use Ruby.

I made a hash with some URL's and i'm looping through this hash and
using the url stored there and getting some info from a site that i'm
storing into a file. Im attaching my code. This actually works

Now I would like to store the information in a db table. So I would like
to modify my code. I created an sqlite3 db with two tables called 'urls'
and 'stats'. 'urls' have the columns 'ID','Description', 'URL' and
'stats' have the columns 'ID','Date','Title'.

Now, I need to connect to my database and fetch this data. I have used
DataMapper for this.

My questions:
Should I query the rows one by one?
Should I query the whole table store into a hash and the loop through?
When I fetch the row how can I get the column URL?

My working code:
require 'rubygems'
require 'mechanize'

url = { "1" => "http://www.ruby-forum.com/",
    "2" => "http://www.stackoverflow.com/",
    "3" => "http://www.ruby-doc.org/"
  }
time=Time.now.strftime("%Y-%m-%d %H:%M")
File.open("Stats_Test.txt","a+") do |vStats|
#Loop - hash
  url.each do |number,url|
      agent = Mechanize.new
      page = agent.get(url)
      title = agent.page.title.strip
      vStats.puts "#{time} #{number} #{title}\n"
  end
end

My modified code so far:

require 'rubygems'
require 'mechanize'
require 'data_mapper'

#Sqlite3 connection
DataMapper.setup(:default, 'sqlite:///Ruby/Stats/StatsDB.sqlite')

class Url
  include DataMapper::Resource
end
class Stats
  include DataMapper::Resource
end

time=Time.now.strftime("%Y-%m-%d %H:%M")

int = 0
num = 3

#What to do here?
while int<num do
   ds = Url.get(int)
   #ds has the first row. Now how do I get the column 'URL'?
   agent = Mechanize.new
   page = agent.get(ds:url) #I want to give it the url from the table
   title = agent.page.title.strip #get the title from website
   #write title to database table stats
    #Havent thgought of this yet
end

Any suggestions are apreciated!

Br
cristian

···

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

[Stefan_Codrescu]

Have you considered using active record?

StefaN

···

On Jun 20, 2013, at 7:09 AM, cristian cristian <lists@ruby-forum.com> wrote:

Hi all!

I'm a complete beginner at this so please bear with me...I dont program
at all but if I need to then I have been trying to use Ruby.

I made a hash with some URL's and i'm looping through this hash and
using the url stored there and getting some info from a site that i'm
storing into a file. Im attaching my code. This actually works

Now I would like to store the information in a db table. So I would like
to modify my code. I created an sqlite3 db with two tables called 'urls'
and 'stats'. 'urls' have the columns 'ID','Description', 'URL' and
'stats' have the columns 'ID','Date','Title'.

Now, I need to connect to my database and fetch this data. I have used
DataMapper for this.

My questions:
Should I query the rows one by one?
Should I query the whole table store into a hash and the loop through?
When I fetch the row how can I get the column URL?

My working code:
require 'rubygems'
require 'mechanize'

url = { "1" => "http://www.ruby-forum.com/&quot;,
   "2" => "http://www.stackoverflow.com/&quot;,
   "3" => "http://www.ruby-doc.org/&quot;
}
time=Time.now.strftime("%Y-%m-%d %H:%M")
File.open("Stats_Test.txt","a+") do |vStats|
#Loop - hash
url.each do |number,url|
     agent = Mechanize.new
     page = agent.get(url)
     title = agent.page.title.strip
     vStats.puts "#{time} #{number} #{title}\n"
end
end

My modified code so far:

require 'rubygems'
require 'mechanize'
require 'data_mapper'

#Sqlite3 connection
DataMapper.setup(:default, 'sqlite:///Ruby/Stats/StatsDB.sqlite')

class Url
include DataMapper::Resource
end
class Stats
include DataMapper::Resource
end

time=Time.now.strftime("%Y-%m-%d %H:%M")

int = 0
num = 3

#What to do here?
while int<num do
  ds = Url.get(int)
  #ds has the first row. Now how do I get the column 'URL'?
  agent = Mechanize.new
  page = agent.get(ds:url) #I want to give it the url from the table
  title = agent.page.title.strip #get the title from website
  #write title to database table stats
   #Havent thgought of this yet
end

Any suggestions are apreciated!

Br
cristian

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

[Panagiotis_Atmatzidi (Panagiotis Atmatzidis)]

Hello,

···

On 20 Ιουν 2013, at 15:09 , cristian cristian <lists@ruby-forum.com> wrote:

Hi all!

I'm a complete beginner at this so please bear with me...I dont program
at all but if I need to then I have been trying to use Ruby.

I made a hash with some URL's and i'm looping through this hash and
using the url stored there and getting some info from a site that i'm
storing into a file. Im attaching my code. This actually works

Now I would like to store the information in a db table. So I would like
to modify my code. I created an sqlite3 db with two tables called 'urls'
and 'stats'. 'urls' have the columns 'ID','Description', 'URL' and
'stats' have the columns 'ID','Date','Title'.

Now, I need to connect to my database and fetch this data. I have used
DataMapper for this.

My questions:
Should I query the rows one by one?
Should I query the whole table store into a hash and the loop through?
When I fetch the row how can I get the column URL?

My working code:
require 'rubygems'
require 'mechanize'

url = { "1" => "http://www.ruby-forum.com/&quot;,
   "2" => "http://www.stackoverflow.com/&quot;,
   "3" => "http://www.ruby-doc.org/&quot;
}
time=Time.now.strftime("%Y-%m-%d %H:%M")
File.open("Stats_Test.txt","a+") do |vStats|
#Loop - hash
url.each do |number,url|
     agent = Mechanize.new
     page = agent.get(url)
     title = agent.page.title.strip
     vStats.puts "#{time} #{number} #{title}\n"
end
end

My modified code so far:

require 'rubygems'
require 'mechanize'
require 'data_mapper'

#Sqlite3 connection
DataMapper.setup(:default, 'sqlite:///Ruby/Stats/StatsDB.sqlite')

-----------------

class Url
include DataMapper::Resource
end
class Stats
include DataMapper::Resource
end

-----------------

Why do you create these two classes? It's not wrong, I just don't get what are you trying to do here?

I don't see any 'properties' defined there anyway.

time=Time.now.strftime("%Y-%m-%d %H:%M")

int = 0
num = 3

#What to do here?
while int<num do
  ds = Url.get(int)
  #ds has the first row. Now how do I get the column 'URL'?

Try "Url.get.all" and "Url.get.all.class" to see what kind of object is returned.

  agent = Mechanize.new
  page = agent.get(ds:url) #I want to give it the url from the table
  title = agent.page.title.strip #get the title from website
  #write title to database table stats
   #Havent thgought of this yet

http://datamapper.org/docs/create_and_destroy.html

end

Any suggestions are apreciated!

Br
cristian

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

Panagiotis (atmosx) Atmatzidis

email: atma@convalesco.org
URL: http://www.convalesco.org
GnuPG ID: 0x1A7BFEC5
gpg --keyserver pgp.mit.edu --recv-keys 1A7BFEC5
--
The wise man said: "Never argue with an idiot. They bring you down to their level and beat you with experience."

[Hassan_Schroeder]

Should I query the rows one by one?
Should I query the whole table store into a hash and the loop through?
When I fetch the row how can I get the column URL?

I would suggest you read http://datamapper.org/getting-started.html
and try thinking in OO terms. The whole point of an ORM is to deal
with *objects*, not rows in a database.

------- snip ------

int = 0
num = 3

#What to do here?
while int<num do
   ds = Url.get(int)
   #ds has the first row. Now how do I get the column 'URL'?

Throw all of the above away

class Site # I'd rename your Url class
  include DataMapper::Resource
  property :id, Serial
  property :description, String
  property :url, String # url in lowercase
end

Site.all.each do |site|
   puts site.url
end

HTH,

···

On Thu, Jun 20, 2013 at 6:09 AM, cristian cristian <lists@ruby-forum.com> wrote:
--
Hassan Schroeder ------------------------ hassan.schroeder@gmail.com

Hassan Schroeder on about.me

twitter: @hassan

[7stud2 (7stud --)]

Thank you!
This works great!

Regarding my questions. What really happens 'behind the scenes' when an
object is used as proposed? -> "Site.all.each do..."

If a db-table is very big, can the same approach be used?

Br
cristian

···

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

[7stud2 (7stud --)]

I think you're use "where" rather than "all" to return records matching
a specific set of criteria.

···

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

[7stud2 (7stud --)]

Ok,
Now I have been trying to change the code so I can store the information
in another DB-table. I have been searching for information but I know im
doing something terrible wrong. I would like to store the dateTime and
site.id (from table/class Site)into a table called Stats. I thought that
this could be done within the "Site.all.each do..." but I get a lot of
errors...

Any suggestions how to continue?
Br
cristian

This is the code so far:

require 'rubygems'
require 'mechanize'
require 'data_mapper'

#Sqlite3 connection
DataMapper.setup(:default, 'sqlite:///Ruby/Stats/StatsDB.sqlite')

class Site # I'd rename your Url class
  include DataMapper::Resource
  property :id, Serial
  property :description, String
  property :url, String # url in lowercase
end

class Stats
  include DataMapper::Resource
  property :id, Serial
  property :date, DateTime
  property :url_id, Integer
end

time=Time.now.strftime("%Y-%m-%d %H:%M")

Site.all.each do |site|
   puts site.url

   Stats.create(
    :Date => time,
    :url_ID => site.id
    )
end

···

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

[7stud2 (7stud --)]

Hmm, ok Thank you. I actually made it work before I read your post. Any
way, I have a (beginner) question. I still dont understand how
everything works. When I do "Site.all.each do..." - Im creating an
object that holds all data from the table Sites? That object is in
memory? What if the table is very big, wouldnt that be memory consuming?
Or is the object actually querying the database for each iteration?

Any way. Thank you all. Now I will try to do the same with active_record
as suggested above.

Br
cristian

···

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

[7stud2 (7stud --)]

Hello all....again

When I try to do this with activerecord I get some kind of sqlite3
problem that I cant resolve. My code and error message below. Maybe any
one of you can see what Im doing wrong? This work fine with datamapper
(and sqlite3).

This is my code:

require 'rubygems'
require 'mechanize'
require 'active_record'
#require 'sqlite3'
ActiveRecord::Base.establish_connection(
    :adapter => "sqlite3",
    :host => "localhost",
    :dbfile => "\Ruby\Stats\StatsDB.sqlite"
  )

class Site < ActiveRecord::Base
end

class Estadistica < ActiveRecord::Base
end

time=Time.now.strftime("%Y-%m-%d %H:%M")

Site.all.each do |site|

    Estadistica.create(
    :date => time,
    :site_id => site.id
    )
end

Error message:

C:/Ruby200/lib/ruby/site_ruby/2.0.0/rubygems/core_ext/kernel_require.rb:45:in
`r
equire': Please install the sqlite3 adapter: `gem install
activerecord-sqlite3-a
dapter` (cannot load such file -- sqlite3/sqlite3_native) (LoadError)
        from
C:/Ruby200/lib/ruby/site_ruby/2.0.0/rubygems/core_ext/kernel_requir
e.rb:45:in `require'
        from
C:/Ruby200/lib/ruby/gems/2.0.0/gems/activesupport-3.2.13/lib/active
_support/dependencies.rb:251:in `block in require'
        from
C:/Ruby200/lib/ruby/gems/2.0.0/gems/activesupport-3.2.13/lib/active
_support/dependencies.rb:236:in `load_dependency'
        from
C:/Ruby200/lib/ruby/gems/2.0.0/gems/activesupport-3.2.13/lib/active
_support/dependencies.rb:251:in `require'
        from
C:/Ruby200/lib/ruby/gems/2.0.0/gems/sqlite3-1.3.7-x86-mingw32/lib/s
qlite3.rb:6:in `rescue in <top (required)>'
        from
C:/Ruby200/lib/ruby/gems/2.0.0/gems/sqlite3-1.3.7-x86-mingw32/lib/s
qlite3.rb:2:in `<top (required)>'
        from
C:/Ruby200/lib/ruby/site_ruby/2.0.0/rubygems/core_ext/kernel_requir
e.rb:45:in `require'
        from
C:/Ruby200/lib/ruby/site_ruby/2.0.0/rubygems/core_ext/kernel_requir
e.rb:45:in `require'
        from
C:/Ruby200/lib/ruby/gems/2.0.0/gems/activesupport-3.2.13/lib/active
_support/dependencies.rb:251:in `block in require'
        from
C:/Ruby200/lib/ruby/gems/2.0.0/gems/activesupport-3.2.13/lib/active
_support/dependencies.rb:236:in `load_dependency'
        from
C:/Ruby200/lib/ruby/gems/2.0.0/gems/activesupport-3.2.13/lib/active
_support/dependencies.rb:251:in `require'
        from
C:/Ruby200/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_
record/connection_adapters/sqlite3_adapter.rb:4:in `<top (required)>'
        from
C:/Ruby200/lib/ruby/site_ruby/2.0.0/rubygems/core_ext/kernel_requir
e.rb:45:in `require'
        from
C:/Ruby200/lib/ruby/site_ruby/2.0.0/rubygems/core_ext/kernel_requir
e.rb:45:in `require'
        from
C:/Ruby200/lib/ruby/gems/2.0.0/gems/activesupport-3.2.13/lib/active
_support/dependencies.rb:251:in `block in require'
        from
C:/Ruby200/lib/ruby/gems/2.0.0/gems/activesupport-3.2.13/lib/active
_support/dependencies.rb:236:in `load_dependency'
        from
C:/Ruby200/lib/ruby/gems/2.0.0/gems/activesupport-3.2.13/lib/active
_support/dependencies.rb:251:in `require'
        from
C:/Ruby200/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_
record/connection_adapters/abstract/connection_specification.rb:50:in
`resolve_h
ash_connection'
        from
C:/Ruby200/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_
record/connection_adapters/abstract/connection_specification.rb:29:in
`spec'
        from
C:/Ruby200/lib/ruby/gems/2.0.0/gems/activerecord-3.2.13/lib/active_
record/connection_adapters/abstract/connection_specification.rb:130:in
`establis
h_connection'
        from Stats_testDB3.rb:15:in `<main>'

···

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

[7stud2 (7stud --)]

Yes I did. Didnt work...

C:\Ruby\Stats>gem install activerecord-sqlite3-adapter
ERROR: Could not find a valid gem 'activerecord-sqlite3-adapter' (>= 0)
in any
repository

···

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

[7stud2 (7stud --)]

Well, if I 'require sqlite3'...I get another error:

C:/Ruby200/lib/ruby/site_ruby/2.0.0/rubygems/core_ext/kernel_require.rb:45:in
`r
equire': cannot load such file -- sqlite3/sqlite3_native (LoadError)
        from
C:/Ruby200/lib/ruby/site_ruby/2.0.0/rubygems/core_ext/kernel_requir
e.rb:45:in `require'
        from
C:/Ruby200/lib/ruby/gems/2.0.0/gems/sqlite3-1.3.7-x86-mingw32/lib/s
qlite3.rb:6:in `rescue in <top (required)>'
        from
C:/Ruby200/lib/ruby/gems/2.0.0/gems/sqlite3-1.3.7-x86-mingw32/lib/s
qlite3.rb:2:in `<top (required)>'
        from
C:/Ruby200/lib/ruby/site_ruby/2.0.0/rubygems/core_ext/kernel_requir
e.rb:110:in `require'
        from
C:/Ruby200/lib/ruby/site_ruby/2.0.0/rubygems/core_ext/kernel_requir
e.rb:110:in `rescue in require'
        from
C:/Ruby200/lib/ruby/site_ruby/2.0.0/rubygems/core_ext/kernel_requir
e.rb:35:in `require'
        from Stats_testDB3.rb:4:in `<main>'

/c

···

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

[7stud2 (7stud --)]

Ruby 2.0 on Windows and Sqlite3 don't get along well.

Have a look at this discussion:
https://www.ruby-forum.com/topic/4413168#new

and this related thread:
https://www.ruby-forum.com/topic/4415126#new

···

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

[7stud2 (7stud --)]

Thank you for the suggestion Joel. I followed Scotts instructions and
yet theres a problem. Why does this work fine when I use datamapper?

Here's the error message when I follow Scotts instructions.

C:\knapsack>gem install sqlite3 --platform=ruby
--with-opt-dir=C:/Knapsack
ERROR: While executing gem ... (OptionParser::InvalidOption)
    invalid option: --with-opt-dir=C:/Knapsack

Br
cristian

···

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

[7stud2 (7stud --)]

Try using the files and details I've provided near the end of this
thread:
https://www.ruby-forum.com/topic/4415126#new

···

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

[7stud2 (7stud --)]

I downloaded your zip-file and extracted in "c:\knapsack\sqlite" and did
this:

C:\knapsack\sqlite>gem install sqlite3 --platform=ruby --
--with-sqlite3-include
=c:/knapsack/sqlite/include --with-sqlite3-lib=c:/knapsack/sqlite/lib
--with-sqlite3-dir=c:/knapsack/sqlite/bin
ERROR: Error installing sqlite3:
        The 'sqlite3' native gem requires installed build tools.

Please update your PATH to include build tools or download the DevKit
from 'http://rubyinstaller.org/downloads' and follow the instructions
at 'http://github.com/oneclick/rubyinstaller/wiki/Development-Kit'

···

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

[7stud2 (7stud --)]

You need the development kit installed.
http://rubyinstaller.org/downloads/

There's a guide which tells you which one you should use:

    Ruby 1.8.6 to 1.9.3: tdm-32-4.5.2
    Ruby 2.0.0: mingw64-32-4.7.2
    Ruby 2.0.0 x64 (64bits): mingw64-64-4.7.2

···

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

[7stud2 (7stud --)]

Grrr...still I got a problem.... 'failed to build gem native
extension'...
...

C:\Ruby200\devkit>gem install sqlite3 --platform=ruby --
--with-sqlite3-include=
c:/knapsack/sqlite/include --with-sqlite3-lib=c:/knapsack/sqlite/lib
--with-sqli
te3-dir=c:/knapsack/sqlite/bin
Temporarily enhancing PATH to include DevKit...
Building native extensions with:
'--with-sqlite3-include=c:/knapsack/sqlite/incl
ude --with-sqlite3-lib=c:/knapsack/sqlite/lib
--with-sqlite3-dir=c:/knapsack/sql
ite/bin'
This could take a while...
ERROR: Error installing sqlite3:
        ERROR: Failed to build gem native extension.

    C:/Ruby200/bin/ruby.exe extconf.rb
--with-sqlite3-include=c:/knapsack/sqlite
/include --with-sqlite3-lib=c:/knapsack/sqlite/lib
--with-sqlite3-dir=c:/knapsac
k/sqlite/bin
checking for sqlite3.h... yes
checking for sqlite3_libversion_number() in -lsqlite3... no
sqlite3 is missing. Install SQLite3 from http://www.sqlite.org/ first.
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of necessary
libraries and/or headers. Check the mkmf.log file for more details.
You may
need configuration options.

Provided configuration options:
        --with-opt-dir
        --without-opt-dir
        --with-opt-include
        --without-opt-include=${opt-dir}/include
        --with-opt-lib
        --without-opt-lib=${opt-dir}/lib
        --with-make-prog
        --without-make-prog
        --srcdir=.
        --curdir
        --ruby=C:/Ruby200/bin/ruby
        --with-sqlite3-dir
        --with-sqlite3-include=${sqlite3-dir}/include
        --with-sqlite3-lib=${sqlite3-dir}/
        --enable-local
        --disable-local
        --with-sqlite3lib
        --without-sqlite3lib

Gem files will remain installed in
C:/Ruby200/lib/ruby/gems/2.0.0/gems/sqlite3-1
.3.7 for inspection.
Results logged to
C:/Ruby200/lib/ruby/gems/2.0.0/gems/sqlite3-1.3.7/ext/sqlite3/
gem_make.out

···

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

[7stud2 (7stud --)]

PS. I installed devkit and did a test installation...

···

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

[7stud2 (7stud --)]

Please try using the instructions and the zip file I posted in the
thread I mentioned earlier.

···

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

[7stud2 (7stud --)]

Thank you for your help and patience Joel. I have done all steps
mentioned above and still I get an error message. I give up . I can
continue to work with datamapper that works for me.

Maybe I'll try active record on my mac when I get home.

Br
cristian

···

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