使用Ruby进行PostgreSQL的CRUD操作

2 年 ago

4 minutes

使用 Arch Linux 安装库文件。

sudo pacman -S ruby-pg

我会创建一个名为”city”的数据库。

create database city owner = scott;

连接信息

user='scott'
password='*****'
data_base='city'

version_check.rb
#! /usr/bin/ruby
#
# version_check.rb
#
# 2019年5月14日
# ——————————————————————–
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(“服务器版本: ” + row[0].to_s)
#
connection.finish
#
puts “*** 结束 ***”
#
# ——————————————————————–

执行结果
$ ./version_check.rb
*** 开始 ***
服务器版本: {“version”=>”PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 13.1.1 20230429, 64-bit”}
*** 结束 ***

创建

postgre_create.rb
#! /usr/bin/ruby
# -*- encoding: utf-8 -*-
#
# ruby/create/postgre_create.rb
#
# 2019年5月14日
# ———————————————————————
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 “*** 结束 ***”
#
# ———————————————————————

读取

postgre_read.rb
#! /usr/bin/ruby
# -*- coding: utf-8 -*-
#
# postgre_read.rb
#
# 2019年5月14日
#
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 “*** 结束 ***”
# ——————————————————————–

更新

postgre_update.rb
#! /usr/bin/ruby
# -*- coding: utf-8 -*-
#
# postgre_update.rb
#
# 2019年5月14日
#
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 “*** 结束 ***”
# ————————————————————

删除

postgre_delete.rb
#! /usr/bin/ruby
# -*- coding: utf-8 -*-
#
# postgre_delete.rb
#
# 2019年5月14日
#
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 “*** 结束 ***”
# ————————————————————