DBI in R

This article is a work in progress.

What is DBI?

DBI is a database interface definition for communication between R and Relational Database Management Systems.

Drivers

dbDriver
Load database drivers.
Example
mysql.driver <- RMySQL::MySQL()
dbUnloadDriver
Unload database drivers.
Example
dbUnloadDriver(mysql.driver)

Connections

dbConnect
Create a connection to a database management systems.
Example
mysql.connection <- dbConnect(mysql.driver, username="$USERNAME", password="$PASSWORD")
dbDisconnect
Closes a connection, discards all pending work, and frees resources (e.g., memory, sockets).
Example
dbDisconnect(mysql.connection)
dbListConnections
List currently open connections.
Example
dbListConnections(mysql.driver)

Executing SQL Statements

dbSendQuery
Execute a statement on a given database connection.
Example
Temp
dbBind
Bind values to a parameterised statement.
Example
Temp
dbCallProc
Executes a stored procedure using the specified connection.
Example
Temp
dbGetQuery
Send query, retrieve results and then clear result set.
Example
Temp
dbFetch
Fetch records from a previously executed query.
Example
Temp
dbClearResult
Clear a result set.
Example
Temp
dbGetException
Get database management system exceptions.
Example
Temp

Transactions

dbBegin
Begin SQL transactions.
Example
Temp
dbCommit
Commit SQL transactions.
Example
Temp
dbRollback
Rollback SQL transactions.
Example
Temp

Importing & Exporting Tables

dbExistsTable
Does a table exist?
Example
Temp
dbWriteTable
Copy data frames to database tables.
Example
Temp
dbReadTable
Copy database tables to data frames.
Example
Temp
dbRemoveTable
Remove a table from the database.
Example
Temp

Metadata

dbGetInfo
Returns metadata for any given object that is an DBIObject.
Example
Temp
dbGetStatement
Get the statement associated with a result set.
Example
Temp
dbGetRowCount
The number of rows fetched so far.
Example
Temp
dbGetRowsAffected
The number of rows affected by data modifying query.
Example
Temp
dbColumnInfo
Information about result types.
Example
Temp
dbHasCompleted
Has the operation completed?
Example
Temp
dbListTables
List remote tables.
Example
Temp
dbListFields
List field names of a remote table.
Example
Temp
dbListResults
A list of all pending results.
Example
Temp

Additional Resources

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.