[R-SIG-Finance] RMySQL - setDefaults requires clear text name/password?
Mark Knecht
markknecht at gmail.com
Sat Dec 21 00:16:35 CET 2013
On Fri, Dec 20, 2013 at 1:49 PM, Paul Gilbert <pgilbert902 at gmail.com> wrote:
> 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.
>
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 (DownloadNewData){
+ 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)
}
More information about the R-SIG-Finance
mailing list