Rのこと。

記事は引っ越し作業中。2023年中までに引っ越しを完了させてブログは削除予定

RMySQLパッケージの使い方まとめ

はじめに

MySQLのRインターフェースであるRMySQLパッケージの基本的な使い方をまとめておく。今まで、MySQL側でやってしまっていたのをRと接続して、色々やりたいことがあったので、そのためのまとめ。MySQLにRからインサートするも適時、参照。

RとMySQLのコネクション

このパッケージを使用すると、RからMySQLデータベースに接続し、RからMySQLSQLを使って操作できる。まずは、MySQLでデータベースを作成する。

# テスト用DBなので、セキュリティ気にせずrootで入る
mysql> mysql -u root -p
mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)

MySQLとRのコネクションを確立する。

library(RMySQL)
library(tidyverse)

con <- dbConnect(
  drv = RMySQL::MySQL(),
  dbname = "test_db",
  user = "root",
  password = "pass",
  host = "localhost",
  port = 3306
)

セキュリティー上、MySQLにRからアクセスする際に、パスワードをコンソールで実行するのはよくないので、my.cnfに下記の設定を記述しておけばよいかも。

# mysqldセクション: mysqlサーバーへの設定
[mysqld]
default_authentication_plugin=mysql_native_password
loose-local-infile=1

# clientセクション: mysqlクライアントツールへの設定
[client]
user="****"
password="****"
host=****
port=3306

コネクションの情報はsummary()で確認できる。

summary(con)
<MySQLConnection:0,6>
  User:   root 
  Host:   localhost 
  Dbname: test_db 
  Connection type: Localhost via UNIX socket 

Results:

テーブルのインポートと読み込み

dbWriteTable()でテーブルを作成する。mtcarsでテーブルを作成することにする。今はデータベースの中は空の状態。

# list tables
dbListTables(con) 
character(0)

mtcarsの中身はこうなっている。

df <- mtcars %>% 
  tibble::rownames_to_column() %>% 
  tibble::as_tibble()

df
# A tibble: 32 x 12
   rowname        mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
   <chr>        <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
 1 Mazda RX4     21       6  160    110  3.9   2.62  16.5     0     1     4     4
 2 Mazda RX4 W…  21       6  160    110  3.9   2.88  17.0     0     1     4     4
 3 Datsun 710    22.8     4  108     93  3.85  2.32  18.6     1     1     4     1
 4 Hornet 4 Dr…  21.4     6  258    110  3.08  3.22  19.4     1     0     3     1
 5 Hornet Spor…  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2
 6 Valiant       18.1     6  225    105  2.76  3.46  20.2     1     0     3     1
 7 Duster 360    14.3     8  360    245  3.21  3.57  15.8     0     0     3     4
 8 Merc 240D     24.4     4  147.    62  3.69  3.19  20       1     0     4     2
 9 Merc 230      22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2
10 Merc 280      19.2     6  168.   123  3.92  3.44  18.3     1     0     4     4
# … with 22 more rows

では実行する。dbWriteTable()で書き込み、dbListTables()dbListFields()で確認している。

# write dataframe to DB
dbWriteTable(con, "mtcars", df)
[1] TRUE

# show table 
dbListTables(con)
[1] "mtcars"

# show col
dbListFields(con, "mtcars")
 [1] "row_names" "rowname"   "mpg"       "cyl"       "disp"      "hp"       
 [7] "drat"      "wt"        "qsec"      "vs"        "am"        "gear"     
[13] "carb"  

MySQLでも確認できるか、ターミナルから確認しておく。問題なさそう。

mysql> use test_db;
Database changed

mysql> select * from mtcars limit 5;
+-----------+-------------------+------+------+------+------+------+-------+-------+------+------+------+------+
| row_names | rowname           | mpg  | cyl  | disp | hp   | drat | wt    | qsec  | vs   | am   | gear | carb |
+-----------+-------------------+------+------+------+------+------+-------+-------+------+------+------+------+
| 1         | Mazda RX4         |   21 |    6 |  160 |  110 |  3.9 |  2.62 | 16.46 |    0 |    1 |    4 |    4 |
| 2         | Mazda RX4 Wag     |   21 |    6 |  160 |  110 |  3.9 | 2.875 | 17.02 |    0 |    1 |    4 |    4 |
| 3         | Datsun 710        | 22.8 |    4 |  108 |   93 | 3.85 |  2.32 | 18.61 |    1 |    1 |    4 |    1 |
| 4         | Hornet 4 Drive    | 21.4 |    6 |  258 |  110 | 3.08 | 3.215 | 19.44 |    1 |    0 |    3 |    1 |
| 5         | Hornet Sportabout | 18.7 |    8 |  360 |  175 | 3.15 |  3.44 | 17.02 |    0 |    0 |    3 |    2 |
+-----------+-------------------+------+------+------+------+------+-------+-------+------+------+------+------+
5 rows in set (0.00 sec)

mysql> select count(1) from mtcars;
+----------+
| count(1) |
+----------+
|       32 |
+----------+
1 row in set (0.00 sec)

Rからこのテーブルを呼び出すには、dbReadTable()を使う。SQLを書いて呼び出してもいい。

 # get table 
dbReadTable(con, "mtcars")
               rowname  mpg cyl  disp  hp drat    wt  qsec vs am gear carb
1            Mazda RX4 21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
2        Mazda RX4 Wag 21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
3           Datsun 710 22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
4       Hornet 4 Drive 21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
5    Hornet Sportabout 18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2

SQLの実行

このパッケージには、RがMySQLSQLを送り、クエリを実行するための2つの関数dbSendQuery()dbGetQuery()が用意されている。dbGetQuery()は、リクエストを送信し、結果をRに返す。例えばDESCRIBEしてみる。

dbGetQuery(con, "DESCRIBE mtcars")
       Field   Type Null Key Default Extra
1  row_names   text  YES        <NA>      
2    rowname   text  YES        <NA>      
3        mpg double  YES        <NA>      
4        cyl double  YES        <NA>      
5       disp double  YES        <NA>      
6         hp double  YES        <NA>      
7       drat double  YES        <NA>      
8         wt double  YES        <NA>      
9       qsec double  YES        <NA>      
10        vs double  YES        <NA>      
11        am double  YES        <NA>      
12      gear double  YES        <NA>      
13      carb double  YES        <NA>      

SELECT文だとこんな感じ。

dbGetQuery(con, "SELECT * FROM mtcars WHERE gear=5;")
  row_names        rowname  mpg cyl  disp  hp drat    wt qsec vs am gear carb
1        27  Porsche 914-2 26.0   4 120.3  91 4.43 2.140 16.7  0  1    5    2
2        28   Lotus Europa 30.4   4  95.1 113 3.77 1.513 16.9  1  1    5    2
3        29 Ford Pantera L 15.8   8 351.0 264 4.22 3.170 14.5  0  1    5    4
4        30   Ferrari Dino 19.7   6 145.0 175 3.62 2.770 15.5  0  1    5    6
5        31  Maserati Bora 15.0   8 301.0 335 3.54 3.570 14.6  0  1    5    8

ALTERUPDATEなどのテーブルのデータを変更するクエリを実行した場合、dbGetQuery()は何も返さない。

dbGetQuery(con, "ALTER TABLE `mtcars` DROP COLUMN `rowname`")
 0 列 0 行のデータフレーム 

dbGetQuery(con, "UPDATE `mtcars` SET `carb` = `carb`*100")
 0 列 0 行のデータフレーム 

dbReadTable(con, "mtcars")
    mpg cyl  disp  hp drat    wt  qsec vs am gear carb
1  21.0   6 160.0 110 3.90 2.620 16.46  0  1    4  400
2  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4  400
3  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4  100
4  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3  100
5  18.7   8 360.0 175 3.15 3.440 17.02  0  0    3  200

これらのクエリを実行する場合、dbSendQuery()を使用するのがよい。理由は、Rのオブジェクトとして結果も返すことができるため。これを変数にいれると、この結果に関する情報を取得できる。まずはサンプルデータをもとに戻す。

dbGetQuery(con, "DROP TABLE mtcars;")
 0 列 0 行のデータフレーム 

dbWriteTable(con, "mtcars", df)
[1] TRUE

UPDATEdbSendQuery()で実行する。MySQLResultクラスが返され、dbGetStatement()では実行したSQLが確認でき、dbGetRowsAffected()では影響のあった行数を確認できる。

res <- dbSendQuery(con, "UPDATE `mtcars` SET `carb` = `carb`*100")
class(res)
[1] "MySQLResult"
attr(,"package")
[1] "RMySQL"

dbGetStatement(res)
[1] "UPDATE `mtcars` SET `carb` = `carb`*100"

dbGetRowsAffected(res)
[1] 32

他にも、dbColumnInfo()では返されたデータの情報が見れたりする。

res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE rowname LIKE 'Merc%';") 
dbColumnInfo(res)
        name    Sclass      type length
1  row_names character BLOB/TEXT  65535
2    rowname character BLOB/TEXT  65535
3        mpg    double    DOUBLE     22
4        cyl    double    DOUBLE     22
5       disp    double    DOUBLE     22
6         hp    double    DOUBLE     22
7       drat    double    DOUBLE     22
8         wt    double    DOUBLE     22
9       qsec    double    DOUBLE     22
10        vs    double    DOUBLE     22
11        am    double    DOUBLE     22
12      gear    double    DOUBLE     22
13      carb    double    DOUBLE     22

dbFetch()すればデータフレームとして受け取ることもできる。

dbFetch(res)
  row_names     rowname  mpg cyl  disp  hp drat   wt qsec vs am gear carb
1         8   Merc 240D 24.4   4 146.7  62 3.69 3.19 20.0  1  0    4  200
2         9    Merc 230 22.8   4 140.8  95 3.92 3.15 22.9  1  0    4  200
3        10    Merc 280 19.2   6 167.6 123 3.92 3.44 18.3  1  0    4  400
4        11   Merc 280C 17.8   6 167.6 123 3.92 3.44 18.9  1  0    4  400
5        12  Merc 450SE 16.4   8 275.8 180 3.07 4.07 17.4  0  0    3  300
6        13  Merc 450SL 17.3   8 275.8 180 3.07 3.73 17.6  0  0    3  300
7        14 Merc 450SLC 15.2   8 275.8 180 3.07 3.78 18.0  0  0    3  300

# resを削除
dbClearResult(res)
[1] TRUE

トランザクション

データの一貫性を保つために、dbBegin()dbRollback()を組み合わせれば、トランザクション処理が実行できる。am = 0のものを削除してみる。

dbBegin(con)
[1] TRUE

dbSendQuery(con, "DELETE FROM mtcars WHERE am = 0") 
<MySQLResult:2,0,7>

dbReadTable(con, "mtcars") # query is executed
          rowname  mpg cyl  disp  hp drat    wt  qsec vs am gear carb
1       Mazda RX4 21.0   6 160.0 110 3.90 2.620 16.46  0  1    4  400
2   Mazda RX4 Wag 21.0   6 160.0 110 3.90 2.875 17.02  0  1    4  400
3      Datsun 710 22.8   4 108.0  93 3.85 2.320 18.61  1  1    4  100
18       Fiat 128 32.4   4  78.7  66 4.08 2.200 19.47  1  1    4  100
19    Honda Civic 30.4   4  75.7  52 4.93 1.615 18.52  1  1    4  200
20 Toyota Corolla 33.9   4  71.1  65 4.22 1.835 19.90  1  1    4  100
26      Fiat X1-9 27.3   4  79.0  66 4.08 1.935 18.90  1  1    4  100
27  Porsche 914-2 26.0   4 120.3  91 4.43 2.140 16.70  0  1    5  200
28   Lotus Europa 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5  200
29 Ford Pantera L 15.8   8 351.0 264 4.22 3.170 14.50  0  1    5  400
30   Ferrari Dino 19.7   6 145.0 175 3.62 2.770 15.50  0  1    5  600
31  Maserati Bora 15.0   8 301.0 335 3.54 3.570 14.60  0  1    5  800
32     Volvo 142E 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4  200

この状態だとR側では変更処理がされているがMySQL側からみるとまだ変更されていない。

mysql> select * from mtcars;
+-----------+---------------------+------+------+-------+------+------+-------+-------+------+------+------+------+
| row_names | rowname             | mpg  | cyl  | disp  | hp   | drat | wt    | qsec  | vs   | am   | gear | carb |
+-----------+---------------------+------+------+-------+------+------+-------+-------+------+------+------+------+
| 1         | Mazda RX4           |   21 |    6 |   160 |  110 |  3.9 |  2.62 | 16.46 |    0 |    1 |    4 |  400 |
| 2         | Mazda RX4 Wag       |   21 |    6 |   160 |  110 |  3.9 | 2.875 | 17.02 |    0 |    1 |    4 |  400 |
| 3         | Datsun 710          | 22.8 |    4 |   108 |   93 | 3.85 |  2.32 | 18.61 |    1 |    1 |    4 |  100 |
【略】
| 30        | Ferrari Dino        | 19.7 |    6 |   145 |  175 | 3.62 |  2.77 |  15.5 |    0 |    1 |    5 |  600 |
| 31        | Maserati Bora       |   15 |    8 |   301 |  335 | 3.54 |  3.57 |  14.6 |    0 |    1 |    5 |  800 |
| 32        | Volvo 142E          | 21.4 |    4 |   121 |  109 | 4.11 |  2.78 |  18.6 |    1 |    1 |    4 |  200 |
+-----------+---------------------+------+------+-------+------+------+-------+-------+------+------+------+------+
32 rows in set (0.00 sec)

この状態で、R側での操作であるam = 0のものを削除するSQLをキャンセルしたい。そんな時にdbRollback()を使う。インサートしたいが途中でエラーがでるかもしれないので、Rからtry-catchでロールバックすることをMySQLで考えたりもするが、MYSQLDDLとかの処理方法によっては思ったように動かないこともあるので注意。

dbRollback(con)
[1] TRUE

dbReadTable(con, "mtcars") # query is cancelled
               rowname  mpg cyl  disp  hp drat    wt  qsec vs am gear carb
1            Mazda RX4 21.0   6 160.0 110 3.90 2.620 16.46  0  1    4  400
2        Mazda RX4 Wag 21.0   6 160.0 110 3.90 2.875 17.02  0  1    4  400
3           Datsun 710 22.8   4 108.0  93 3.85 2.320 18.61  1  1    4  100
4       Hornet 4 Drive 21.4   6 258.0 110 3.08 3.215 19.44  1  0    3  100
5    Hornet Sportabout 18.7   8 360.0 175 3.15 3.440 17.02  0  0    3  200
【略】
28        Lotus Europa 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5  200
29      Ford Pantera L 15.8   8 351.0 264 4.22 3.170 14.50  0  1    5  400
30        Ferrari Dino 19.7   6 145.0 175 3.62 2.770 15.50  0  1    5  600
31       Maserati Bora 15.0   8 301.0 335 3.54 3.570 14.60  0  1    5  800
32          Volvo 142E 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4  200

Learning-R-Programmingにあるようなコードの場合、SQLiteでは、テーブルの作成自体がなかったようにロールバックが機能するが、MySQLの場合は、そうはならない。これはトランザクションDDLなどの関係によるっぽい。ちなみに本来はトランザクションはコミットかロールバックで終わるが、dbWriteTable()で同時にコミットされていると考えても問題なさそう。ここらへんがRからMySQLを操作するときによくわかっていない。MYSQLのAUTO COMMITモードのような感じなのか・・・?

# ちょっと変更している
chunk_size <- 5
dbBegin(con)
set.seed(123)
res <- tryCatch({
  for (i in 1:6) {
    cat("Processing chunk", i, "\n")
    if (runif(1) <= 0.2) stop("Data error")
    chunk <- data.frame(id = ((i - 1L) * chunk_size):(i * chunk_size - 1L), type = LETTERS[[i]])
    dbWriteTable(con, "products", chunk, append = TRUE, row.names = FALSE)
  }
}, error = function(e) {
  warning("An error occurs: ", e, "\nRolling back", immediate. = TRUE)
  dbRollback(con)
})

Processing chunk 1 
Processing chunk 2 
Processing chunk 3 
Processing chunk 4 
Processing chunk 5 
Processing chunk 6 
 value[[3L]](cond) で警告がありました: 
  An error occurs: Error in doTryCatch(return(expr), name, parentenv, handler): Data error

Rolling back

mysql> select * from products;
+------+------+
| id   | type |
+------+------+
|    0 | A    |
|    1 | A    |
|    2 | A    |
|    3 | A    |
|    4 | A    |
|    5 | B    |
|    6 | B    |
|    7 | B    |
|    8 | B    |
|    9 | B    |
|   10 | C    |
|   11 | C    |
|   12 | C    |
|   13 | C    |
|   14 | C    |
|   15 | D    |
|   16 | D    |
|   17 | D    |
|   18 | D    |
|   19 | D    |
|   20 | E    |
|   21 | E    |
|   22 | E    |
|   23 | E    |
|   24 | E    |
+------+------+
25 rows in set (0.00 sec)

こんな感じでインサートに変更しても結果は変わらない。

df <- tibble(id = 1:length(LETTERS), name = LETTERS)

chunk_size <- nrow(df)
dbBegin(con)
set.seed(123)
dbGetQuery(con, "CREATE TABLE products (id int, name varchar(10));")

res <- tryCatch({
  for (i in 1:nrow(df)) {
    cat("Processing chunk", i, "\n")
    if (runif(1) <= 0.1) stop("Data error")
    query <- query <- paste0("INSERT INTO products VALUES(",df[i,1], ",'", df[i,2], "');")
    # EXECUTE QUERY
    dbSendQuery(con, query)
    }
  },
  error = function(e) {
    warning("An error occurs: ", e, "\nRolling back", immediate. = TRUE)
    dbRollback(con)
})

Processing chunk 1 
Processing chunk 2 
Processing chunk 3 
Processing chunk 4 
Processing chunk 5 
Processing chunk 6 
Processing chunk 7 
Processing chunk 8 
Processing chunk 9 
Processing chunk 10 
Processing chunk 11 
Processing chunk 12 
 value[[3L]](cond) で警告がありました: 
  An error occurs: Error in doTryCatch(return(expr), name, parentenv, handler): Data error

Rolling back

mysql> select * from products;
+------+------+
| id   | name |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
|    4 | D    |
|    5 | E    |
|    6 | F    |
|    7 | G    |
|    8 | H    |
|    9 | I    |
|   10 | J    |
|   11 | K    |
+------+------+
11 rows in set (0.00 sec)

以上でおしまし。最後に作業がおわれば、MySQLからのコネクションを解除しておく。

dbDisconnect(DB)
[1] TRUE