低レベルインフラエンジニアの忘備録

Linux系の各種作業を自分用の忘備録として残しています

【Ruby】ruby-mysqlを使用してMySQLに接続

事前にやっておくこと

# gem install ruby-mysql

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」のレコードが削除されています