Posted at

Ruby で PostgreSQL の CRUD

接続情報


.env

user='****'

password='*****'
data_base='city'

1) PostgreSQL のバージョンを調べる方法です。


version_check.rb

#! /usr/bin/ruby

#
# version_check.rb
#
# May/14/2019
# --------------------------------------------------------------------
require 'pg'
require 'dotenv'
#
puts "*** 開始 ***"
#
Dotenv.load
user = ENV['user']
password = ENV['password']
data_base = ENV['data_base']
#
connection = PG::connect(:host => "localhost",
:user =>user, :password =>password, :dbname =>data_base)
#
row = connection.exec("SELECT VERSION()")
#
puts("Server version: " + row[0].to_s)
#
connection.finish
#
puts "*** 終了 ***"
#
# --------------------------------------------------------------------

2) Create


postgre_create.rb

#! /usr/bin/ruby

# -*- encoding: utf-8 -*-
#
# ruby/create/postgre_create.rb
#
# May/14/2019
# ---------------------------------------------------------------------
require 'pg'
require 'dotenv'
#
# ---------------------------------------------------------------------
def dict_append_proc (dict_aa,id,name,population,date_mod)
unit = {}
unit['name'] = name
unit['population'] = population
unit['date_mod'] = date_mod
key = id.to_s
dict_aa[key] = unit
return dict_aa
end
# ---------------------------------------------------------------------
def prepare_data_proc ()
dict_aa={}
dict_aa=dict_append_proc(dict_aa,'t3461',"広島",83162,"2006-3-12")
dict_aa=dict_append_proc(dict_aa,'t3462',"福山",97385,"2006-4-27")
dict_aa=dict_append_proc(dict_aa,'t3463',"東広島",58724,"2006-5-8")
dict_aa=dict_append_proc(dict_aa,'t3464',"呉",25914,"2006-1-15")
dict_aa=dict_append_proc(dict_aa,'t3465',"尾道",84721,"2006-5-21")
dict_aa=dict_append_proc(dict_aa,'t3466',"竹原",21853,"2006-4-7")
dict_aa=dict_append_proc(dict_aa,'t3467',"三次",42637,"2006-4-8")
dict_aa=dict_append_proc(dict_aa,'t3468',"大竹",53129,"2006-8-15")
dict_aa=dict_append_proc(dict_aa,'t3469',"府中",71956,"2006-12-21")
return dict_aa
end
# ---------------------------------------------------------------------
def create_proc (connection)
sql_str="create TABLE cities (" \
+ "id varchar(10) NOT NULL PRIMARY KEY," \
+ "name varchar(20)," \
+ "population int," \
+ "date_mod varchar(40))"
puts sql_str
connection.exec(sql_str)
end
# ------------------------------------------------------------
def drop_proc (connection)
sql_str="drop table cities"
puts sql_str
connection.exec(sql_str)
end
# ---------------------------------------------------------------------
def insert_proc (connection,id,name,population,date_mod)
sql_str="INSERT into cities " \
+ "(id, Name, Population, date_mod) values \
('
#{id}', '#{name}',#{population},'#{date_mod}')"
# puts sql_str
connection.exec(sql_str)
end
# ---------------------------------------------------------------------
puts "*** 開始 ***"
#
Dotenv.load
user = ENV['user']
password = ENV['password']
data_base = ENV['data_base']
#
connection = PG::connect(:host => "localhost",
:user =>user, :password =>password, :dbname =>data_base)
#
#
dict_aa=prepare_data_proc()
#
drop_proc(connection)
create_proc(connection)
#
dict_aa.each {|key,value |
insert_proc(connection,key,value['name'], \
value['population'],value['date_mod'])
}
#
connection.finish
#
puts "*** 終了 ***"
#
# ---------------------------------------------------------------------

3) Read


postgre_read.rb

#! /usr/bin/ruby

# -*- coding: utf-8 -*-
#
# postgre_read.rb
#
# May/14/2019
#
require 'pg'
require 'dotenv'
#
# --------------------------------------------------------------------
puts "*** 開始 ***"
#
Dotenv.load
user = ENV['user']
password = ENV['password']
data_base = ENV['data_base']
#
connection = PG::connect(:host => "localhost",
:user =>user, :password =>password, :dbname =>data_base)
#
table = connection.exec('select * from cities order by ID')
#
table.each {|row|
print(row["id"] + "\t")
print(row["name"] + "\t")
print(row["population"] + "\t")
print(row["date_mod"] + "\n")
}
#
connection.finish
#
puts "*** 終了 ***"
# --------------------------------------------------------------------

4) Update


postgre_update.rb

#! /usr/bin/ruby

# -*- coding: utf-8 -*-
#
# postgre_update.rb
#
# May/14/2019
#
require 'pg'
require 'dotenv'
#
# ------------------------------------------------------------
puts "*** 開始 ***"
#
Dotenv.load
user = ENV['user']
password = ENV['password']
data_base = ENV['data_base']
#
connection = PG::connect(:host => "localhost",
:user =>user, :password =>password, :dbname =>data_base)
#
id_in = ARGV[0]
population_in = ARGV[1].to_i
#
puts id_in,population_in
#
date_mod=Date.today
sql_str="UPDATE cities SET population='#{population_in}', DATE_MOD='#{date_mod}' where ID = '#{id_in}'"
puts sql_str
connection.exec(sql_str)
#
connection.finish
#
puts "*** 終了 ***"
# ------------------------------------------------------------

5) Delete


postgre_delete.rb

#! /usr/bin/ruby

# -*- coding: utf-8 -*-
#
# postgre_delete.rb
#
# May/14/2019
#
require 'pg'
require 'dotenv'
#
# ------------------------------------------------------------
puts "*** 開始 ***"
#
Dotenv.load
user = ENV['user']
password = ENV['password']
data_base = ENV['data_base']
#
connection = PG::connect(:host => "localhost",
:user =>user, :password =>password, :dbname =>data_base)
#
id_in = ARGV[0]
#
puts id_in
#
sql_str="DELETE from cities where ID = '#{id_in}'"
puts sql_str
connection.exec(sql_str)
#
#
connection.finish
#
puts "*** 終了 ***"
# ------------------------------------------------------------