Skip to content

RMySQL - setDefaults requires clear text name/password?

6 messages · Zachary Mayer, Paul Gilbert, Mark Knecht

Original
Mark Knecht
# 
I'm using RMySQL saving data from Yahoo. It's working nicely, but as
best I can tell the setDefaults function seems to require me to put
the MySQL account name and password in clear text in my R program
which I don't like. I.e. - the one commented out below works, the one
currently not commented out fails later when using
getSymbols(,src="MySQL")

As best I can tell everything else I'm using - most of which I think
comes from the DBI package - is using /home/mark/.my.cnf to get these
values. I can connect to the database, read and write it, etc. It's
only the getSymbols command I'm having trouble with.

Is there a way around this that keeps the password out of my files and
out of the file.Rout file when I run in batch mode?

Thanks,
Mark


if (UseMySQL){
  if (MyAdjust) { dbName = paste0(dbName, "Adjust")}
  dbc = dbConnect(MySQL(), dbname=dbName)
#  setDefaults(getSymbols.MySQL, user="mark", password="TmpPasswd",
dbname=dbName)
  setDefaults(getSymbols.MySQL, dbname=dbName)
}
Mark Knecht
#
On Fri, Dec 20, 2013 at 11:03 AM, Mark Knecht <markknecht at gmail.com> wrote:
Answering myself, it appears the answer is no unless there's some
system-level magic out there. I don't see any way for this function as
currently written to pick up the values I'd like it to be able to read
from the .my.cnf file.

- Mark
function (Symbols, env, return.class = "xts", db.fields = c("date",
    "o", "h", "l", "c", "v", "a"), field.names = NULL, user = NULL,
    password = NULL, dbname = NULL, host = "localhost", port = 3306,
    ...)
{
    importDefaults("getSymbols.MySQL")
    this.env <- environment()
    for (var in names(list(...))) {
        assign(var, list(...)[[var]], this.env)
    }
    if (missing(verbose))
        verbose <- FALSE
    if (missing(auto.assign))
        auto.assign <- TRUE
    if ("package:DBI" %in% search() || require("DBI", quietly = TRUE)) {
        if ("package:RMySQL" %in% search() || require("RMySQL",
            quietly = TRUE)) {
        }
        else {
            warning(paste("package:", dQuote("RMySQL"), "cannot be loaded"))
        }
    }
    else {
        stop(paste("package:", dQuote("DBI"), "cannot be loaded."))
    }
    if (is.null(user) || is.null(password) || is.null(dbname)) {
        stop(paste("At least one connection argument (", sQuote("user"),
            sQuote("password"), sQuote("dbname"), ") is not set"))
    }
    con <- dbConnect("MySQL", user = user, password = password,
        dbname = dbname, host = host, port = port)
    db.Symbols <- dbListTables(con)
    if (length(Symbols) != sum(Symbols %in% db.Symbols)) {
        missing.db.symbol <- Symbols[!Symbols %in% db.Symbols]
        warning(paste("could not load symbol(s): ", paste(missing.db.symbol,
            collapse = ", ")))
        Symbols <- Symbols[Symbols %in% db.Symbols]
    }
    for (i in 1:length(Symbols)) {
        if (verbose) {
            cat(paste("Loading ", Symbols[[i]], paste(rep(".",
                10 - nchar(Symbols[[i]])), collapse = ""), sep = ""))
        }
        query <- paste("SELECT ", paste(db.fields, collapse = ","),
            " FROM ", Symbols[[i]], " ORDER BY date")
        rs <- dbSendQuery(con, query)
        fr <- fetch(rs, n = -1)
        fr <- xts(as.matrix(fr[, -1]), order.by = as.Date(fr[,
            1], origin = "1970-01-01"), src = dbname, updated = Sys.time())
        colnames(fr) <- paste(Symbols[[i]], c("Open", "High",
            "Low", "Close", "Volume", "Adjusted"), sep = ".")
        fr <- convert.time.series(fr = fr, return.class = return.class)
        if (auto.assign)
            assign(Symbols[[i]], fr, env)
        if (verbose)
            cat("done\n")
    }
    dbDisconnect(con)
    if (auto.assign)
        return(Symbols)
    return(fr)
}
<environment: namespace:quantmod>
Paul Gilbert
# 
I think there is some "system-level magic" that getSymbols.MySQL is
breaking. The MySQL driver will actually find the information in the 
.my.cnf file if it is called without user and password, but the code 
does not try that:


 >      if (is.null(user) || is.null(password) || is.null(dbname)) {
 >          stop(paste("At least one connection argument (", sQuote("user"),
 >              sQuote("password"), sQuote("dbname"), ") is not set"))
 >      }
 >      con <- dbConnect("MySQL", user = user, password = password,
 >          dbname = dbname, host = host, port = port)

I think it will be fixed by (untested)

    if ( is.null(dbname))
        stop('dbname must be specified')
    if (is.null(user) & is.null(password) )
      con <- dbConnect("MySQL", dbname = dbname)
    else
      con <- dbConnect("MySQL", user = user, password = password,
          dbname = dbname, host = host, port = port)

This could probably do something better if the user manages to specify 
one of user or password, or host, or port.

Paul.
On 13-12-20 04:30 PM, Mark Knecht wrote:
Mark Knecht
#
On Fri, Dec 20, 2013 at 1:49 PM, Paul Gilbert <pgilbert902 at gmail.com> wrote:
This looks like a good direction for a potential solution.
Unfortunately my R function foo seems a bit lacking to make it work.

I copied the function I posted earlier to my own function file, called
it MySQLgetSymbols and added your idea. I unfortunately have two
problems:

1) R complains that 'missing' is being misused. Being that I don't
care about verbose of auto.assign for testing I just commented those
out.

#  if (missing(verbose))
    verbose <- FALSE
#  if (missing(auto.assign))
    auto.assign <- TRUE

2) The larger problem is that it needs a function called
convert.time.series. I don't know where that comes from but I suspect
it has something to do with the "<environment: namespace:quantmod>" I
see when I print out the function (see second post if you still have
it) but I don't know how to use in this case.

Thanks,
Mark
+   if (!UseMySQL){
+     getSymbolsCont(BuyHoldSymbol,  from = Date_Start, to = Date_End,
adjust = MyAdjust, src=SymbolSrc)
+   } else {
+  #   getSymbols(BuyHoldSymbol, src="MySQL")
+     MySQLgetSymbols(BuyHoldSymbol,  from = Date_Start, to =
Date_End, dbname=dbName)
+   }
+ }
Error: could not find function "convert.time.series"
MySQLgetSymbols =function (Symbols, env, return.class = "xts",
db.fields = c("date",
                            "o", "h", "l", "c", "v", "a"), field.names
= NULL, user = NULL,
                           password = NULL, dbname = NULL, host =
"localhost", port = 3306,
                           ...
                          )
{
  importDefaults("getSymbols.MySQL")
  this.env <- environment()
  for (var in names(list(...))) {
    assign(var, list(...)[[var]], this.env)
  }
#  if (missing(verbose))
    verbose <- FALSE
#  if (missing(auto.assign))
    auto.assign <- TRUE
  if ("package:DBI" %in% search() || require("DBI", quietly = TRUE)) {
    if ("package:RMySQL" %in% search() || require("RMySQL",
                                                  quietly = TRUE)) {
    }
    else {
      warning(paste("package:", dQuote("RMySQL"), "cannot be loaded"))
    }
  }
  else {
    stop(paste("package:", dQuote("DBI"), "cannot be loaded."))
  }

#   if (is.null(user) || is.null(password) || is.null(dbname)) {
#     stop(paste("At least one connection argument (", sQuote("user"),
#                sQuote("password"), sQuote("dbname"), ") is not set"))
#   }

if ( is.null(dbname))
  stop('dbname must be specified')


#This section is new:

if (is.null(user) & is.null(password) )
  con <- dbConnect("MySQL", dbname = dbname)
else
  con <- dbConnect("MySQL", user = user, password = password,
                   dbname = dbname, host = host, port = port)

# End of edit


  db.Symbols <- dbListTables(con)
  if (length(Symbols) != sum(Symbols %in% db.Symbols)) {
    missing.db.symbol <- Symbols[!Symbols %in% db.Symbols]
    warning(paste("could not load symbol(s): ", paste(missing.db.symbol,
                                                      collapse = ", ")))
    Symbols <- Symbols[Symbols %in% db.Symbols]
  }
  for (i in 1:length(Symbols)) {
    if (verbose) {
      cat(paste("Loading ", Symbols[[i]], paste(rep(".",
                                                    10 -
nchar(Symbols[[i]])), collapse = ""), sep = ""))
    }
    query <- paste("SELECT ", paste(db.fields, collapse = ","),
                   " FROM ", Symbols[[i]], " ORDER BY date")
    rs <- dbSendQuery(con, query)
    fr <- fetch(rs, n = -1)
    fr <- xts(as.matrix(fr[, -1]), order.by = as.Date(fr[,
                                                         1], origin =
"1970-01-01"), src = dbname, updated = Sys.time())
    colnames(fr) <- paste(Symbols[[i]], c("Open", "High",
                                          "Low", "Close", "Volume",
"Adjusted"), sep = ".")
    fr <- convert.time.series(fr = fr, return.class = return.class)
    if (auto.assign)
      assign(Symbols[[i]], fr, env)
    if (verbose)
      cat("done\n")
  }
  dbDisconnect(con)
  if (auto.assign)
    return(Symbols)
  return(fr)
}
Mark Knecht
#
On Fri, Dec 20, 2013 at 3:16 PM, Mark Knecht <markknecht at gmail.com> wrote:
<SNIP>
<SNIP>
<SNIP>

So apparently this function is internal to quantmod and undocumented.

I think a better way to test this out would be to create a diff patch
for quantmod that makes the change. That may be a stretch for me but
if I can find some time I'll try it.

Cheers,
Mark