Ruby で PostgreSQL の CRUD

Arch Linux でのライブラリーのインストール

sudo pacman -S ruby-pg

city というデータベースを作成しておきます。

create database city owner = scott;

接続情報

.env

user='scott'
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 "*** 終了 ***"
#
# --------------------------------------------------------------------

実行結果

$ ./version_check.rb 
*** 開始 ***
Server version: {"version"=>"PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 13.1.1 20230429, 64-bit"}
*** 終了 ***

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 "*** 終了 ***"
# ------------------------------------------------------------