jzuccollo
10/24/2019 - 3:13 PM

SQL connection in R with config file

library(RMariaDB)
library(dbplyr)
library(dplyr)

# Connect to the SQL server using configuration file
dbconnector <- function(dbname, table = NULL) {
  con <- RMariaDB::dbConnect(
    RMariaDB::MariaDB(),
    group = dbname,
    default.file = path.expand("~/my.cnf")
  )
  if (is.null(table)) {
    return(con)
  } else {
    return(dplyr::tbl(con, table))
  }
}

# example of use
sen_tbl <- dbconnector("sen", table = "sen_all")
edubase_tbl <- dbconnector("edubase", table = "edubaseall")
joined_tbl <-
  edubase_tbl %>%
  dplyr::left_join(sen_tbl, by = c("URN"))
[alltables]
database=DBNAME
user=USERNAME
password=PASSWORD
host=DBPATH