RSQLite

RSQLiteは、Rからデータベースを使用する最も簡単な方法です。なぜなら、パッケージ自体に SQLite が含まれており、外部ソフトウェアは必要ありません。この vignette では、SQLiteデータベースを使用するための基本的な方法を説明します。

RSQLite は DBI と互換性のあるインターフェースで、主に DBI パッケージで定義された関数を使用します。:

新しいデータベースの作成

新しいSQLiteデータベースを作成するには、dbConnect() にファイル名を与えるだけです。:

mydb <- dbConnect(RSQLite::SQLite(), "my-db.sqlite")
dbDisconnect(mydb)
unlink("my-db.sqlite")

一時的なデータベースが必要な場合は、""（ディスク上のデータベースの場合）、":memory:"または"file::memory:" （インメモリのデータベースの場合）のいずれかを使用してください。このデータベースは、接続を切ると自動的に削除されます。

mydb <- dbConnect(RSQLite::SQLite(), "")
dbDisconnect(mydb)

データの読み込み

Rのデータフレームを SQLite データベースにコピーするには、dbWriteTable() を使うと簡単です。:

mydb <- dbConnect(RSQLite::SQLite(), "")
dbWriteTable(mydb, "mtcars", mtcars)
dbWriteTable(mydb, "iris", iris)
dbListTables(mydb)
#> [1] "iris"   "mtcars"

クエリ

dbGetQuery() でクエリを発行します。:

dbGetQuery(mydb, 'SELECT * FROM mtcars LIMIT 5')
#>    mpg cyl disp  hp drat    wt  qsec vs am gear carb
#> 1 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
#> 2 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
#> 3 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
#> 4 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
#> 5 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2

すべてのR変数名が有効なSQL変数名とは限らないので、"でエスケープする必要があるかもしれません。

dbGetQuery(mydb, 'SELECT * FROM iris WHERE "Sepal.Length" < 4.6')
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> 1          4.4         2.9          1.4         0.2  setosa
#> 2          4.3         3.0          1.1         0.1  setosa
#> 3          4.4         3.0          1.3         0.2  setosa
#> 4          4.5         2.3          1.3         0.3  setosa
#> 5          4.4         3.2          1.3         0.2  setosa

ユーザからの値をクエリに挿入する必要がある場合，paste()を使ってはいけません! この方法では悪意のある攻撃者が、データベースに損害を与えたり、機密情報を漏らしたりする可能性のあるSQLを簡単に挿入することができてしまいます。代わりに，パラメータ化されたクエリを使用してください。:

dbGetQuery(mydb, 'SELECT * FROM iris WHERE "Sepal.Length" < :x', 
  params = list(x = 4.6))
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> 1          4.4         2.9          1.4         0.2  setosa
#> 2          4.3         3.0          1.1         0.1  setosa
#> 3          4.4         3.0          1.3         0.2  setosa
#> 4          4.5         2.3          1.3         0.3  setosa
#> 5          4.4         3.2          1.3         0.2  setosa

バッチ処理されたクエリ

クエリを実行した結果がメモリに収まらない場合、dbSendQuery()、dbFetch()、dbClearResults() を用いて結果を一括して取得することができます。デフォルトでは、dbFetch() は利用可能なすべての行を取得します: n を使用して、取得する最大行数を設定してください。

rs <- dbSendQuery(mydb, 'SELECT * FROM mtcars')
while (!dbHasCompleted(rs)) {
  df <- dbFetch(rs, n = 10)
  print(nrow(df))
}
#> [1] 10
#> [1] 10
#> [1] 10
#> [1] 2
dbClearResult(rs)

複数パラメータのクエリ

同じパラメータ化されたクエリを異なるパラメータで実行するには、同じ方法を使います。パラメータを設定するには dbBind() を呼び出します。

rs <- dbSendQuery(mydb, 'SELECT * FROM iris WHERE "Sepal.Length" < :x')
dbBind(rs, params = list(x = 4.5))
nrow(dbFetch(rs))
#> [1] 4
dbBind(rs, params = list(x = 4))
nrow(dbFetch(rs))
#> [1] 0
dbClearResult(rs)

dbBind() への呼び出しに複数のパラメータを渡すことができます。:

rs <- dbSendQuery(mydb, 'SELECT * FROM iris WHERE "Sepal.Length" = :x')
dbBind(rs, params = list(x = seq(4, 4.4, by = 0.1)))
nrow(dbFetch(rs))
#> [1] 4
dbClearResult(rs)

ステートメント

DBI には新しい関数 dbSendStatement() および dbExecute() があります。これらは dbSendQuery() および dbGetQuery() の対になるもので、テーブルへのレコードの挿入、テーブルの更新、エンジンのパラメータの設定など、表形式の結果を返さない SQL 文を扱うことができます。

現在は強制されていませんが、次のような場合に新しい関数を使用することが推奨されています。

dbExecute(mydb, 'DELETE FROM iris WHERE "Sepal.Length" < 4')
#> [1] 0
rs <- dbSendStatement(mydb, 'DELETE FROM iris WHERE "Sepal.Length" < :x')
dbBind(rs, params = list(x = 4.5))
dbGetRowsAffected(rs)
#> [1] 4
dbClearResult(rs)