Beginners problem with database and datamapper

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

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

Any suggestions are apreciated!

Br
cristian

Have you considered using active record?

StefaN

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

On Thu, Jun 20, 2013 at 6:09 AM, cristian cristian
[email protected] wrote:

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

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,

Hello,

On 20 Ιουν 2013, at 15:09 , cristian cristian [email protected]
wrote:

to modify my code. I created an sqlite3 db with two tables called ‘urls’

#Loop - hash

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

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

Panagiotis (atmosx) Atmatzidis

email: [email protected]
URL: http://www.convalesco.org
GnuPG ID: 0x1A7BFEC5
gpg --keyserver pgp.mit.edu --recv-keys 1A7BFEC5

I think you’d use “where” rather than “all” to return records matching
a specific set of criteria.

On Tue, Jun 25, 2013 at 1:15 AM, cristian cristian
[email protected] wrote:

but I get a lot of errors…

Any suggestions how to continue?

In general, posting the errors you don’t understand (or can’t figure
out by googling) is a good idea :slight_smile:

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

class Stat # singular
include DataMapper::Resource
property :id, Serial
property :date, DateTime
property :site_id, Integer # use the name of the thing you’re
referring to
end

DataMapper.finalize

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
)

randomly capitalizing things doesn’t work well :slight_smile:

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

end

HTH,

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

Br
cristian

On Tue, Jun 25, 2013 at 9:45 AM, cristian cristian
[email protected] wrote:

When I do “Site.all.each do…” - Im creating an
object that holds all data from the table Sites? That object is in
memory?

Yes.

What if the table is very big, wouldnt that be memory consuming?

Yes, the object in memory is memory-consuming :slight_smile:

Whether that’s a problem in practical terms is a different question,
and the answer depends on multiple factors.

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

Am 02.08.2013 10:36, schrieb cristian cristian:

require ‘mechanize’

)

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)

Did you try that?

Regards,
Marcus

Hello all…again :slight_smile:

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:inrequire’
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:inload_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:inrescue 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:inrequire’
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:inblock 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:inrequire’
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:inrequire’
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:inblock 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:inrequire’
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:inspec’
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

Am 02.08.2013 10:43, schrieb [email protected]:

require ‘rubygems’
end
:site_id => site.id

Did you try that?

Or try uncommenting the “require ‘sqlite3’” line.

Regards,
Marcus

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

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:inrequire’
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:inrescue 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

/c

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

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

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 ‘Downloads’ and follow the instructions
at ‘Development Kit · oneclick/rubyinstaller Wiki · GitHub

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

You need the development kit installed.

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