[R-SIG-Finance] RMySQL - setDefaults requires clear text name/password?
Mark Knecht
markknecht at gmail.com
Fri Dec 20 22:30:30 CET 2013
On Fri, Dec 20, 2013 at 11:03 AM, Mark Knecht <markknecht at gmail.com> wrote:
> 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)
> }
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
> getSymbols.MySQL
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>
>
More information about the R-SIG-Finance
mailing list