【Ruby】ruby-mysqlを使用してMySQLに接続
事前にやっておくこと
# gem install ruby-mysql
環境
RHEL6.7
ruby2.3.0p0
MySQL5.7.12
使用するテーブル
以下のzaikoテーブルを使用します
mysql> SHOW COLUMNS FROM zaiko; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | maker | text | YES | | NULL | | | type | text | YES | | NULL | | | serial | varchar(100) | YES | | NULL | | | purpose | varchar(20) | YES | | NULL | | | input_date | date | YES | | NULL | | | output_date | date | YES | | NULL | |
mysql> select * from zaiko; +----+--------+-------+--------+----------+------------+-------------+ | id | maker | type | serial | purpose | input_date | output_date | +----+--------+-------+--------+----------+------------+-------------+ | 2 | maker1 | type1 | 001 | purpose1 | 2016-04-01 | 2016-04-02 | | 3 | maker2 | type2 | 002 | purpose2 | 2016-04-03 | 2016-04-04 | | 4 | maker3 | type3 | 003 | purpose3 | 2016-04-03 | 2016-04-04 | | 5 | maker4 | type4 | 004 | purpose4 | 2016-04-05 | 2016-04-06 | | 6 | maker5 | type5 | 005 | purpose5 | 2016-04-06 | 2016-04-07 | +----+--------+-------+--------+----------+------------+-------------+ 5 rows in set (0.01 sec)
簡単なSELECT
◆ソース(mysql_select.rb)
require 'mysql' # DBに接続 my = Mysql::new("127.0.0.1", "[ユーザ名]", "[パスワード]", "[DB名]") # 抽出結果でループ result = my.query("select maker,type,serial,purpose,\ input_date,output_date from zaiko").each do \ |maker,type,serial,purpose,input_date,output_date| # レコードの内容を出力 puts "#{maker},#{type},#{serial},#{purpose},#{input_date},#{output_date}" end # DBの切断 my.close
◆実行結果
# ruby mysql_select.rb maker1,type1,001,purpose1,2016-04-01,2016-04-02 maker2,type2,002,purpose2,2016-04-03,2016-04-04 maker3,type3,003,purpose3,2016-04-03,2016-04-04 maker4,type4,004,purpose4,2016-04-05,2016-04-06 maker5,type5,005,purpose5,2016-04-06,2016-04-07
パラメータを持つSELECT
◆ソース(mysql_select2.rb)
require 'mysql' # DBに接続 my = Mysql::new("127.0.0.1", "root", "Ph/*CyFUf0gt", "test_db") # SQLの作成 stmt = my.prepare("select maker,type,serial,purpose,input_date,\ output_date from zaiko where serial = ?") # SQLの実行&レコードの取り出し tuple = stmt.execute('001').fetch # 結果はtupleに配列で格納される # レコードが存在した場合は結果を出力 if tuple then puts "#{tuple[0]},#{tuple[1]},#{tuple[2]},#{tuple[3]},#{tuple[4]},#{tuple[5]}" end # DBの切断 my.close
◆実行結果
# ruby mysql_select2.rb maker1,type1,001,purpose1,2016-04-01,2016-04-02
INSERT
◆ソース(mysql_insert.rb)
require 'mysql' # DBに接続 my = Mysql::new("127.0.0.1", "root", "Ph/*CyFUf0gt", "test_db") # SQLの作成 stmt = my.prepare("insert into zaiko(maker,type,serial,purpose,input_date,output_date) \ values(?,?,?,?,?,?)") # SQLの実行 stmt.execute('maker6','type1','006','purpose6','2016-04-08','2016-04-09') # DBの切断 my.close
◆mysql_insert.rb実行後のselect実行結果
mysql> select * from zaiko; +----+--------+-------+--------+----------+------------+-------------+ | id | maker | type | serial | purpose | input_date | output_date | +----+--------+-------+--------+----------+------------+-------------+ | 2 | maker1 | type1 | 001 | purpose1 | 2016-04-01 | 2016-04-02 | | 3 | maker2 | type2 | 002 | purpose2 | 2016-04-03 | 2016-04-04 | | 4 | maker3 | type3 | 003 | purpose3 | 2016-04-03 | 2016-04-04 | | 5 | maker4 | type4 | 004 | purpose4 | 2016-04-05 | 2016-04-06 | | 6 | maker5 | type5 | 005 | purpose5 | 2016-04-06 | 2016-04-07 | | 7 | maker6 | type1 | 006 | purpose6 | 2016-04-08 | 2016-04-09 | +----+--------+-------+--------+----------+------------+-------------+ 6 rows in set (0.00 sec)idが7の行が追加されています
UPDATE
◆ソース(mysql_update.rb)
require 'mysql' # DBに接続 my = Mysql::new("127.0.0.1", "root", "Ph/*CyFUf0gt", "test_db") # SQLの作成 stmt = my.prepare("update zaiko set maker=? where serial=?") # SQLの実行 stmt.execute('changed1','001') # DBの切断 my.close
◆mysql_update.rb実行前のselect実行結果
mysql> select * from zaiko; +----+--------+-------+--------+----------+------------+-------------+ | id | maker | type | serial | purpose | input_date | output_date | +----+--------+-------+--------+----------+------------+-------------+ | 2 | maker1 | type1 | 001 | purpose1 | 2016-04-01 | 2016-04-02 | | 3 | maker2 | type2 | 002 | purpose2 | 2016-04-03 | 2016-04-04 | | 4 | maker3 | type3 | 003 | purpose3 | 2016-04-03 | 2016-04-04 | | 5 | maker4 | type4 | 004 | purpose4 | 2016-04-05 | 2016-04-06 | | 6 | maker5 | type5 | 005 | purpose5 | 2016-04-06 | 2016-04-07 | | 7 | maker6 | type1 | 006 | purpose6 | 2016-04-08 | 2016-04-09 | +----+--------+-------+--------+----------+------------+-------------+ 6 rows in set (0.00 sec)今回作成したプログラムではserialが「001」のレコードのmakerを「type1」から「changed1」に変更します
◆mysql_update.rb実行後のselect実行結果
mysql> select * from zaiko; +----+----------+-------+--------+----------+------------+-------------+ | id | maker | type | serial | purpose | input_date | output_date | +----+----------+-------+--------+----------+------------+-------------+ | 2 | changed1 | type1 | 001 | purpose1 | 2016-04-01 | 2016-04-02 | | 3 | maker2 | type2 | 002 | purpose2 | 2016-04-03 | 2016-04-04 | | 4 | maker3 | type3 | 003 | purpose3 | 2016-04-03 | 2016-04-04 | | 5 | maker4 | type4 | 004 | purpose4 | 2016-04-05 | 2016-04-06 | | 6 | maker5 | type5 | 005 | purpose5 | 2016-04-06 | 2016-04-07 | | 7 | maker6 | type1 | 006 | purpose6 | 2016-04-08 | 2016-04-09 | +----+----------+-------+--------+----------+------------+-------------+ 6 rows in set (0.00 sec)serialが「001」のレコードのmakerが「type1」から「changed1」に変更されています
DELETE
◆ソース(mysql_delete.rb)
require 'mysql' # DBに接続 my = Mysql::new("127.0.0.1", "root", "Ph/*CyFUf0gt", "test_db") # SQLの作成 stmt = my.prepare("delete from zaiko where serial=?") # SQLの実行 stmt.execute('006') # DBの切断 my.close
◆mysql_delete.rb実行前のselect実行結果
mysql> select * from zaiko; +----+----------+-------+--------+----------+------------+-------------+ | id | maker | type | serial | purpose | input_date | output_date | +----+----------+-------+--------+----------+------------+-------------+ | 2 | changed1 | type1 | 001 | purpose1 | 2016-04-01 | 2016-04-02 | | 3 | maker2 | type2 | 002 | purpose2 | 2016-04-03 | 2016-04-04 | | 4 | maker3 | type3 | 003 | purpose3 | 2016-04-03 | 2016-04-04 | | 5 | maker4 | type4 | 004 | purpose4 | 2016-04-05 | 2016-04-06 | | 6 | maker5 | type5 | 005 | purpose5 | 2016-04-06 | 2016-04-07 | | 7 | maker6 | type1 | 006 | purpose6 | 2016-04-08 | 2016-04-09 | +----+----------+-------+--------+----------+------------+-------------+ 6 rows in set (0.00 sec)今回作成したプログラムではserialが「006」のレコードを削除します
◆mysql_delete.rb実行後のselect実行結果
mysql> select * from zaiko; +----+----------+-------+--------+----------+------------+-------------+ | id | maker | type | serial | purpose | input_date | output_date | +----+----------+-------+--------+----------+------------+-------------+ | 2 | changed1 | type1 | 001 | purpose1 | 2016-04-01 | 2016-04-02 | | 3 | maker2 | type2 | 002 | purpose2 | 2016-04-03 | 2016-04-04 | | 4 | maker3 | type3 | 003 | purpose3 | 2016-04-03 | 2016-04-04 | | 5 | maker4 | type4 | 004 | purpose4 | 2016-04-05 | 2016-04-06 | | 6 | maker5 | type5 | 005 | purpose5 | 2016-04-06 | 2016-04-07 | +----+----------+-------+--------+----------+------------+-------------+ 5 rows in set (0.01 sec)serialが「006」のレコードが削除されています
