RMySQLパッケージの使い方まとめ
はじめに
MySQLのRインターフェースであるRMySQLパッケージの基本的な使い方をまとめておく。今まで、MySQL側でやってしまっていたのをRと接続して、色々やりたいことがあったので、そのためのまとめ。MySQLにRからインサートするも適時、参照。
RとMySQLのコネクション
このパッケージを使用すると、RからMySQLデータベースに接続し、RからMySQLにSQLを使って操作できる。まずは、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がMySQLにSQLを送り、クエリを実行するための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
ALTER
やUPDATE
などのテーブルのデータを変更するクエリを実行した場合、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
UPDATE
をdbSendQuery()
で実行する。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で考えたりもするが、MYSQLのDDLとかの処理方法によっては思ったように動かないこともあるので注意。
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