Forum: Ruby Store hash contents into DB

Cc7107cb48ea5e9d6b096f4c7fdf2bb1?d=identicon&s=25 Selvag Ruby (selvag1005)
on 2014-06-24 14:37
My hash consists many key-value pairs,

hsh = { 'Ruby' => 5, 'Rails' => 9, 'rspec' => 13, ...}

#some_stubs

and these value of every key is getting the update for three times.

Here, I need to store these into DB like SQlite every time, in the
format,

Key   | First_time_value | Second_time_val | Last_time_val
Ruby  | 5                | 8               | 7
Rails | 9                | 7               | 2
rspec | 13               | 8               | 17
...
D1b31c2021bd98b2ba7c2773225011eb?d=identicon&s=25 Sleeth :!: (sleeth)
on 2014-06-24 23:43
Read 'sqlite3' doc
http://rubydoc.info/gems/sqlite3/1.3.9/frames

require 'sqlite3'
db = SQLite3::Database.new "numbers"

hsh = { 'Ruby' => 5, 'Rails' => 9, 'rspec' => 13}

# this presuppose that already exist a table with data

hsh.each_pair do |k,v|
 # store second, last and current values in an array
 data = db.execute("SELECT second,last FROM numbers WHERE key =
'#{k}'").first + [v]

 # store at db that values
 db.execute("UPDATE numbers SET first=#{data[0]}, second =#{data[1]}
,last =#{data[2]} WHERE key = '#{k}'")

end
54404bcac0f45bf1c8e8b827cd9bb709?d=identicon&s=25 7stud -- (7stud)
on 2014-06-25 19:00
require "sqlite3"

db = SQLite3::Database.new "my_db1.db"

db.execute <<END_OF_CREATE
  CREATE TABLE IF NOT EXISTS my_statistics(
    Key               varchar(30),
    First_time_val    int,
    Second_time_val   int,
    Last_time_val     int
  );
END_OF_CREATE


column_names = [
  "First_time_val",
  "Second_time_val",
  "Last_time_val",
]

data = {
  "Ruby" => 5,
  "Rails" => 9,
  "Rspec" => 13,
}

define_method(:update_db) do |hsh| #ruby 2.0+

  if column_names.size == 3
    my_insert = db.prepare(
      "INSERT INTO my_statistics (Key) VALUES(?)"
    )

    data.keys.each do |name|
      my_insert.execute name
    end
  end

  my_update = db.prepare <<-END_OF_UPDATE
    UPDATE my_statistics
    SET #{column_names.shift} = ?
    WHERE Key = ?;
  END_OF_UPDATE

  hsh.each do |name, num|
    p name, num
    my_update.execute(num, name)
  end

end

update_db(data)

data.update({
  "Ruby" => 8,
  "Rails" => 7,
  "Rspec" => 8,
})

update_db(data)

data.update({
  "Ruby" => 7,
  "Rails" => 2,
  "Rspec" => 17,
})

update_db(data)
54404bcac0f45bf1c8e8b827cd9bb709?d=identicon&s=25 7stud -- (7stud)
on 2014-06-26 00:01
Acch. This seems simpler:


require "sqlite3"


db = SQLite3::Database.new "my_db1.db"

rows = db.execute <<END_OF_CREATE
  CREATE TABLE IF NOT EXISTS my_statistics(
    Key               varchar(30),
    First_time_val    int,
    Second_time_val   int,
    Last_time_val     int
  );
END_OF_CREATE


COLUMN_NAMES = {
  1 => "First_time_val",
  2 => "Second_time_val",
  3 => "Last_time_val",
}

define_method(:update_db) do |data, data_set_count|  #ruby 2.0+

  if data_set_count == 1
    my_insert = db.prepare(
      "INSERT INTO my_statistics
       (Key, #{COLUMN_NAMES[data_set_count]})
       VALUES(?, ?)"
    )

    data.each do |name, num|
      my_insert.execute name, num
    end

  else
    my_update = db.prepare <<-END_OF_UPDATE
      UPDATE my_statistics
      SET #{COLUMN_NAMES[data_set_count]} = ?
      WHERE Key = ?;
    END_OF_UPDATE

    data.each do |name, num|
      my_update.execute(num, name)
    end

  end
end

data = {
  "Ruby" => 5,
  "Rails" => 9,
  "Rspec" => 13,
}

update_db(data, 1)

data.update({
  "Ruby" => 8,
  "Rails" => 7,
  "Rspec" => 8,
})

update_db(data, 2)

data.update({
  "Ruby" => 7,
  "Rails" => 2,
  "Rspec" => 17,
})

update_db(data, 3)
Please log in before posting. Registration is free and takes only a minute.
Existing account

NEW: Do you have a Google/GoogleMail, Yahoo or Facebook account? No registration required!
Log in with Google account | Log in with Yahoo account | Log in with Facebook account
No account? Register here.