Store hash contents into DB

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

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

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)

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)