ruby&sqlite的例子

例子1

require 'sqlite'

db = SQLite::Database.new( "test.db" )

sql = <<SQL
  drop table the_table;

  create table the_table (
    a varchar2(30),
    b varchar2(30)
  );

  insert into the_table values ( 'one', 'two' );
  insert into the_table values ( 'three', 'four' );
  insert into the_table values ( 'five', 'six' );
SQL

db.execute_batch( sql )

db.results_as_hash = true
db.execute( "select * from the_table" ) do |row|
  print row['a'], ',  ', row['b']
  puts
end

例子2

require 'sqlite'
require 'net/http'

def accessNet(stat, url)
  res = Net::HTTP.get(URI.parse(URI.escape(url)))
  res.gsub(/<[^>]*>/m, ' ').gsub(/[\r\n]/m, ' ').gsub(/'/m, '"').scan(/([a-zA-Z-]+)\s/).each do |v|
    v = v.to_s.downcase
    stat[v] = (stat[v]==nil) ? 1 : stat[v]+1
  end
end

def saveData(db, stat)
  sql, i = '', 0
  stat.each do |k, v|
    i = i+1
    sql = "#{sql}insert into the_table values ('#{k}', #{v});"
    if(i%1000==0)
      db.transaction do |db| 
        db.execute_batch( sql )
      end
      sql=''
    end
  end
  db.transaction do |db| 
    db.execute_batch( sql ) if(sql!='')
  end
end


def doinit(db)
    sql = <<SQL
      drop table the_table;
      create table the_table (
        key varchar(300),
        val int
      );
SQL
    db.execute_batch( sql )
end

def printInfo(db)
    # do the select
    db.results_as_hash = true
    db.execute( "select * from the_table t order by t.val desc" ) do |row|
      puts "#{row[0]}, #{row[1]}"
    end
    rows = db.execute( "select count(*) from the_table" )
    p rows
end


db = SQLite::Database.new( "test.db" )

doinit db
stat = {}
accessNet(stat, 'http://www.chinadaily.com.cn/world/2011-08/12/content_13102444.htm')
accessNet(stat, 'http://www.chinadaily.com.cn/world/2011-08/12/content_13099183.htm')
accessNet(stat, 'http://www.chinadaily.com.cn/world/2011-08/13/content_13106532.htm')
accessNet(stat, 'http://www.chinadaily.com.cn/world/2011-08/14/content_13107273.htm')
accessNet(stat, 'http://www.chinadaily.com.cn/world/2011-08/12/content_13099653.htm')
accessNet(stat, 'http://www.chinadaily.com.cn/world/2011-08/14/content_13107262.htm')
accessNet(stat, 'http://www.chinadaily.com.cn/world/2011-08/15/content_13110160.htm')
saveData db, stat
printInfo db

相关的软件下载：

sqlite : http://www.sqlite.org/sqlitedll-2_8_17.zip

ruby gem : http://rubyforge.org/frs/?group_id=254&release_id=1522