CODEWITHSUNDEEP
rdplyrrmysql
Jthorpe
Jthorpe

Reputation: 10167

Connect to MySQL database via dplyr using stored credentials

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?

Upvotes: 4

Views: 625

Answers (1)

Nelson Auner
Nelson Auner

Reputation: 1509

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")

Upvotes: 5

Related Questions