DBI: R Database Interface

データベースと連携するためのパッケージ

> library(DBI)


Attaching package: 'DBI'

The following object is masked from 'package:git2r':

    fetch

バージョン: 0.5

関数名 概略
DBI-package R Database Interface
DBIConnection-class DBIConnection class
DBIDriver-class DBIDriver class
DBIObject-class DBIObject class
DBIResult-class DBIResult class
DBIspec DBI specification
SQL SQL quoting
Table-class Refer to a table nested in a hierarchy (e.g. within a schema)
dbBind Bind values to a parameterised/prepared statement
dbClearResult Clear a result set
dbColumnInfo Information about result types
dbConnect Create a connection to a DBMS
dbDataType Determine the SQL data type of an object
dbDisconnect Disconnect (close) a connection
dbDriver Load and unload database drivers
dbExecute Execute an update statement, query number of rows affected, and then close result set
dbExistsTable Does a table exist?
dbFetch Fetch records from a previously executed query
dbGetException Get DBMS exceptions
dbGetInfo Get DBMS metadata
dbGetQuery Send query, retrieve results and then clear result set
dbGetRowCount The number of rows fetched so far
dbGetRowsAffected The number of rows affected
dbGetStatement Get the statement associated with a result set
dbHasCompleted Completion status
dbIsValid Is this DBMS object still valid?
dbListConnections List currently open connections
dbListFields List field names of a remote table
dbListResults A list of all pending results
dbListTables List remote tables
dbReadTable Copy data frames to and from database tables
dbRemoveTable Remove a table from the database
dbSendQuery Execute a query on a given database connection
dbSendStatement Execute a data manipulation statement on a given database connection
dbWithTransaction Self-contained SQL transactions
make.db.names Make R identifiers into legal SQL identifiers
rownames Convert row names back and forth between columns
sqlAppendTable Insert rows into a table
sqlCreateTable Create a simple table
sqlData Convert a data frame into form suitable for upload to a SQL database
sqlInterpolate Safely interpolate values into an SQL string
transactions Begin/commit/rollback SQL transactions

dbBind

> con <- dbConnect(RSQLite::SQLite(), ":memory:")
> 
> dbWriteTable(con, "iris", iris)
> con %>% dbListTables()
> 
> iris_result <- dbSendQuery(con, "SELECT * FROM iris")
> dbBind(iris_result, list(2.3))
> 
> dbDisconnect(con)

dbClearResult

DBIResult-classオブジェクトの除去

> con <- dbConnect(RSQLite::SQLite(), ":memory:")
> 
> rs <- dbSendQuery(con, "SELECT 1")
> print(dbFetch(rs))
> 
> dbClearResult(rs)
> dbDisconnect(con)

dbColumnInfo

テーブルのデータ型を確認する

> con <- dbConnect(RSQLite::SQLite(), ":memory:")
> 
> rs <- dbSendQuery(con, "SELECT 1 AS a, 2 AS b")
> dbColumnInfo(rs)
> dbFetch(rs)
> 
> dbClearResult(rs)
> dbDisconnect(con)

dbConnect

データベース管理システムへの接続

Arguments

  • drv... データベース管理システムドライバーを指定。{RSQLite}, {RMySQL}, {RPostgreSQL}
  • ...
> (con <- dbConnect(RSQLite::SQLite(), ":memory:"))
> class(con)

dbDisconnect

データ管理システムへの接続を閉じる

> dbDisconnect(con)

dbExecute

> con <- dbConnect(RSQLite::SQLite(), ":memory:")
> 
> dbWriteTable(con, "cars", head(cars, 3))
> dbReadTable(con, "cars")   # there are 3 rows
> dbExecute(con,
+   "INSERT INTO cars (speed, dist) VALUES (1, 1), (2, 2), (3, 3);")
> dbReadTable(con, "cars")   # there are now 6 rows
> 
> dbDisconnect(con)

dbExistsTable

> dbExistsTable(con, "mtcars")

dbGetInfo

接続しているデータベースのメタ情報

> dbConnect(RSQLite::SQLite(), ":memory:") %>% dbGetInfo()

$serverVersion
[1] "3.8.6"

$results
[1] FALSE

dbGetQuery

> con <- dbConnect(RSQLite::SQLite(), ":memory:")
> dbWriteTable(con, "mtcars", mtcars)
> dbGetQuery(con, "SELECT * FROM mtcars LIMIT 5")
> 
> dbDisconnect(con)

dbFetch / fetch

以前実行したクエリーの結果を表示する

> dbFetch(res, n = -1, ...)

dbIsValid

対象のDBMSオブジェクトが有効であるか

> dbIsValid(RSQLite::SQLite())  # TRUE / FALSE

[1] TRUE

dbListTables

> dbListTables(con)

dbReadTable / dbWriteTable

テーブルへの読み込みと書き込み

Arguments

  • conn
  • name
  • ...
  • value
> dbReadTable(conn, name, ...)

dbSendQuery

データベースを操作するクエリーを送る（値は返らない -> dbFetch()で受け取る）

> con <- dbConnect(RSQLite::SQLite(), ":memory:")
> dbWriteTable(con, "mtcars", mtcars)
> 
> res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4;")
> dbFetch(res)
> 
> dbClearResult(res)
> dbDisconnect(con)

sqlAppendTable

> sqlAppendTableTemplate(ANSI(), "iris", iris)

<SQL> INSERT INTO "iris"
  ("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species")
VALUES
  (?1, ?2, ?3, ?4, ?5)

sqlCreateTable

テーブルcreate構文の生成

> sqlCreateTable(ANSI(), "my-table", c(a = "integer", b = "text"))

<SQL> CREATE TABLE "my-table" (
  "a" integer,
  "b" text
)

> sqlCreateTable(ANSI(), "my-table", iris)

<SQL> CREATE TABLE "my-table" (
  "Sepal.Length" DOUBLE,
  "Sepal.Width" DOUBLE,
  "Petal.Length" DOUBLE,
  "Petal.Width" DOUBLE,
  "Species" TEXT
)

> sqlCreateTable(ANSI(), "mtcars", mtcars[, 1:5], row.names = FALSE)

<SQL> CREATE TABLE "mtcars" (
  "mpg" DOUBLE,
  "cyl" DOUBLE,
  "disp" DOUBLE,
  "hp" DOUBLE,
  "drat" DOUBLE
)

sqlData

> con <- dbConnect(RSQLite::SQLite(), ":memory:")
> 
> sqlData(con, head(mtcars))
> 
> dbDisconnect(con)