データベース - R入門

目次

SQLiteを使う

データベース管理システムの一つであるSQLiteをRで使うには、RSQLiteパッケージを使えばよい。

> options(repos = "https://ftp.yz.yamagata-u.ac.jp/pub/cran/")
> install.packages("RSQLite")
 パッケージを ‘C:/Users/○○○/AppData/Local/R/win-library/4.3’ 中にインストールします 
 (‘lib’ が指定されていないため) 
 依存対象 (dependency) ‘DBI’ もインストールします 
（表示省略）

SQLiteのデータベースを作成する

パッケージRSQLiteにおいてデータベースはデータベースのファイルそのものであり、dbConnect関数でファイルを指定して接続するとファイルが存在すれば自動的にファイルが作成され、それがデータベースになる。ファイルが存在すれば、既存のデータベースに接続することになる。

以下は、存在しないprincess.sqliteというファイルを指定して接続を行い、umamusumeというテーブルを書き込んで接続を閉じた例。最後に接続を閉じることを忘れないこと（閉じないとファイルに処理した内容が書き込まれない）。

> file.exists("princess.sqlite")
[1] FALSE
> library(RSQLite)
> con <- dbConnect(SQLite(), "princess.sqlite")
> dtf <- data.frame(
+ no = c(30, 45, 58),
+ name = c("ライスシャワー", "スーパークリーク", "メイショウドトウ"),
+ seiyu = c("石見舞菜香", "優木かな", "和多田美咲"))
> print(dtf)
  no             name      seiyu
1 30   ライスシャワー 石見舞菜香
2 45 スーパークリーク   優木かな
3 58 メイショウドトウ 和多田美咲
> dbWriteTable(con, "umamusume", dtf)
> dbDisconnect(con)
> file.exists("princess.sqlite")
[1] TRUE
> file.info("princess.sqlite")$size
[1] 8192
> file.size("princess.sqlite")
[1] 8192

閉じたデータベースを改めて接続して読み込んでみる。

> con <- dbConnect(SQLite(), "princess.sqlite")
> dbGetQuery(con, "select * from umamusume")
  no             name      seiyu
1 30   ライスシャワー 石見舞菜香
2 45 スーパークリーク   優木かな
3 58 メイショウドトウ 和多田美咲
> dtf <- dbGetQuery(con, "select * from umamusume")
> print(dtf)
  no             name      seiyu
1 30   ライスシャワー 石見舞菜香
2 45 スーパークリーク   優木かな
3 58 メイショウドトウ 和多田美咲
> dbDisconnect(con)

SQLiteのデータベースにテーブルを作成する

dbWriteTable関数にデータフレームを指定すると、そのデータフレームの構造のテーブルが作成される。以下は、あらかじめ作成したデータフレームdtfを書き込んでテーブルを作成した例。dbExistsTable関数は、指定した接続先に指定したテーブルがあるか否かを判定する関数。テーブルの中身を読み込むにはdbReadTable関数を使う。テーブル内のすべてのフィールド名（列名）を得るにはdbListFileds関数を使う。

> library(RSQLite)
> con <- dbConnect(SQLite(), "princess.sqlite")
> dbExistsTable(con, "macross_delta")
[1] FALSE
> print(dtf)
  no      mei          sei  fname     lname      seiyu
1  1   カナメ   バッカニア Kaname Buccaneer 安野希世乃
2  2 フレイア     ヴィオン Freyja      Wion 鈴木みのり
3  3     美雲 ギンヌメール Mikumo  Guynemer 小清水亜美
4  4   マキナ         中島 Makina  Nakajima   西田望見
5  5   レイナ   プラウラー  Reina   Prowler   東山奈央
> dbWriteTable(con, "macross_delta", dtf)
> dbExistsTable(con, "macross_delta")
[1] TRUE

書き込んだテーブルを読み出してみる。

> dtf2 <- dbReadTable(con, "macross_delta")
> print(dtf2)
  no      mei          sei  fname     lname      seiyu
1  1   カナメ   バッカニア Kaname Buccaneer 安野希世乃
2  2 フレイア     ヴィオン Freyja      Wion 鈴木みのり
3  3     美雲 ギンヌメール Mikumo  Guynemer 小清水亜美
4  4   マキナ         中島 Makina  Nakajima   西田望見
5  5   レイナ   プラウラー  Reina   Prowler   東山奈央
> dbListFields(con, "macross_delta")
[1] "no"    "mei"   "sei"   "fname" "lname" "seiyu"
> dbDisconnect(con)

SQLiteのデータベースに含まれるテーブルを一覧表示する

dbListTablesを使う。引数には接続を指定する。以下は、2つのテーブルが含まれたあらかじめ作成してあるデータベースで一覧表示させた例。dbListFields関数は、指定したテーブルのすべてのフィールド名（列名）を返す。

> con <- dbConnect(SQLite(), "princess.sqlite")
> dbListTables(con)
[1] "macross_delta" "umamusume"    
> dbListFields(con, "macross_delta")
[1] "no"    "mei"   "sei"   "fname" "lname" "seiyu"
> dbListFields(con, "umamusume")
[1] "no"    "name"  "seiyu"
> dbDisconnect(con)

MySQLのデータベースに接続する

RMySQLパッケージを使う。以下は、あらかじめサービスが稼働しているMySQLのデータベースを一覧表示させた例。データベースに接続するにはdbConnect関数を使う。dbGetQuery関数を使うことでコマンドを実行することができる。すべての作業が終了したらdbDisconnect関数で接続を切断することを忘れない。以下の1～3, 5はMySQLに最初から組み込まれているデータベースで、4のprincessは別環境で作成したデータベース。以下のとおりにR環境下でも存在を確認することができる。

dbConnect関数使用時は、アカウント、パスワード、接続先を指定する必要がある。以下の例では、rootアカウントでパスワードはpassとしている。hostにlocalhostを指定すると、ローカルホストに接続する。

> library(RMySQL)
> drv <- dbDriver("MySQL")
> con <- dbConnect(drv, user = 'root', password = 'pass', host = 'localhost')
> dbGetQuery(con, "show databases")
            Database
1 information_schema
2              mysql
3 performance_schema
4           princess
5                sys
> dbDisconnect(con)
[1] TRUE

SQLのselect文を使用してデータフレームを操作する

sqldfパッケージのsqldf関数を使う。

> umamusume
  no              name prizemoney       seiyu   dummydate
1  8          ウオッカ    13.0487    大橋彩香  2031/02/03
2 30    ライスシャワー     6.6686  石見舞菜香  2032/11/12
3 45  スーパークリーク     5.5610    優木かな  2033/11/12
4 58  メイショウドトウ     9.2133  和多田美咲  2033/12/31
> library(sqldf)
> sqldf("select * from umamusume")
  no              name prizemoney       seiyu   dummydate
1  8          ウオッカ    13.0487    大橋彩香  2031/02/03
2 30    ライスシャワー     6.6686  石見舞菜香  2032/11/12
3 45  スーパークリーク     5.5610    優木かな  2033/11/12
4 58  メイショウドトウ     9.2133  和多田美咲  2033/12/31
> sqldf("select name, seiyu from umamusume where no >= 30 and no <= 50")
               name       seiyu
1    ライスシャワー  石見舞菜香
2  スーパークリーク    優木かな