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