4

I would like to access a MySQL database using dplyr without having to store my database passwords in plane text R code. Hence, I'd prefer to reference my .my.cnf file, but since the src_mysql has default parameters for host, user, and password, the only way I can find to to to so is via the rather in-elegant:

test_db <- src_mysql("test",
                     default.file=path.expand("~/.my.cnf"),
                     host=NULL,
                     user=NULL,
                     password=NULL)

Is there a less verbose way to connect to a MySQL database from dplyr via stored credentials?

Frank
  • 66,179
  • 8
  • 96
  • 180
Jthorpe
  • 9,756
  • 2
  • 49
  • 64

1 Answers1

5

It seems from Hadley's response to this pull request (Feb 2014, asking to adapt code to allow reading my.cnf) and the documentation by Hadley (where he recommends using my.cnf and that you should pass NULL values) that passing NULL is the desired intent.

If that's bothersome, consider making a function in your .Rprofile with the following:

src_mysql_from_cnf <- function(dbname,
                      dir="~/.my.cnf",
                      host=NULL,
                      user=NULL,
                      password=NULL,
                      ...) {
    if(!(file.exists(dir)))
        stop(sprintf("No such file '%s'",dir))
    dplyr::src_mysql(
        dbname,
        default.file=path.expand(dir),
        # explicitly passing null unless otherwise specified.
        host=host,
        user=user,
        password=password,
        ...)
}

then you can just

test_db <- src_mysql_from_cnf("test")
Nelson Auner
  • 1,421
  • 1
  • 15
  • 21
  • You're creating a function `src_mysql`, but refer to it in your example as `src_mysql_from_cnf`. But I get the idea, thanks! – MS Berends May 21 '17 at 07:12