Using Databases in R

How to connect to Oracle/MySQL/Postgres Databases in R ?

RMySQL
RPostgresSQL
ROracle
dbConnect(drv, ...)

install.packages("RMySQL")
library(DBI)

drv <- dbDriver("Oracle")
con <- dbConnect(RMySQL::MySQL(), dbname="company", host="....", port=3306, user="stud", password="pass")
con <- dbConnect(RSQLite::SQLite(), ":memory:")
con <- dbConnect(RMySQL::MySQL(), dbname = "test")
conn <- dbConnect("MySQL", group = "Analysis")
con2 <- dbConnect("Oracle", username = "user", password = "pwd")

dbListTables(connection)

dbReadTable(connection, "table-name")
tweats <- dbReadTable(con, "tweats")
tables <- lapply(table_names, dbReadTable, conn = con)

dbWriteTable(con, "USArrests", datasets::USArrests, overwrite = TRUE)
dbWriteTable(con, "mtcars", mtcars)
dbWriteTable(conn, "RESULTS", results2000, append = T)

dbRemoveTable(con, "USArrests")

dbExistsTable(conn, name, ...)

dbDisconnect(connection)

MySQL(max.con = 16, fetch.default.rec = 500)

rs <- dbSendQuery(con, "SELECT * FROM USArrests")
res <- dbSendQuery(con, statement = paste("SELECT w.lid, w.wlength, p.off", "FROM WL w, PURGE P",  "WHERE w.lid = p.lid", "ORDER BY w.lid"))
rs <- dbSendQuery(con, "delete * from PURGE as p where p.wlength<0.68")
dbSendQuery(conn, statement, ...)

dbGetQuery(conn, statement, ...)
dbGetQuery(con, 'select * from iris3 where "Sepal.Width" < 6')
dbClearResult(res, ...)
dbGetException(conn, ...)

d1 <- dbFetch(rs, n = 10)
fetch(res, n, ...)
data1 <- fetch(res, n = 100)
data2 <- fetch(res, n = -1)
dim(data1)

dbGetInfo(rs, what = "rowsAffected")

dbHasCompleted(rs)

dbClearResult(rs)

dbCommit(conn, ...)
dbRollback(conn, ...)

Releated aRticles:  Reading files/Writing files     Apply Functions in R