Hello, I'm new to R so this is probably a simple question for somebody. I have an RScript that reads a CSV on the disk using read.table(...). It then does a boxM test using that data. However, I'm now trying to load the same data via an SQL command, but I can't seem to get the data structure defined so R will like it -- using the included "iris" dataset. I've tried these ways of loading the SQL statement into a compatible R Structure: irisQuery <- data(dbGetQuery(conn, "select * from iris")) irisQuery <- data.frame(dbGetQuery(conn, "select * from iris")) irisQuery <- table(dbGetQuery(conn, "select * from iris")) . . . Followed by: boxM(irisQuery[,-5], irisQuery[,5]) Nothing works work. For example, if i use ... irisQuery <- data.frame(dbGetQuery(conn, "select * from iris")) I get this error from R on the boxM test: Error: is.numeric(x) || is.logical(x) is not TRUE The SQL Statement is returning results, but their not CSV. Not sure if that matters. ------------------- So, how do I read a SQL statement into an R structure like I read the CSV using "read.table" so the boxM test will work? Thanks very much in advance. - M Sent from [ProtonMail](https://protonmail.com), Swiss-based encrypted email. [[alternative HTML version deleted]]
Jeff Newmiller
2017-Oct-24 16:40 UTC
[R] Creating a data table (frame?) from a SQL Statement?
Your question desperately needs a reproducible example (a.k.a. "reprex"), because you have to be using contributed packages to do any of this. You also need to clarify whether you are intending to access data already in an external database, or are planning to load it using R and manipulate it with SQL. I suggest that you install the "reprex" and "sqldf" packages and read their documentation and try out their examples. Then when you ask a question use reprex to be sure we see all the steps needed to get to where you are stuck. In particular we need the library calls that preceeded your sample code, but the reprex package will verify that you have it all there before you send the question. FWIW, note that the data function has a specific purpose of pulling data sets by name out of packages into your workspace, and giving it the output of randomly selected functions is not likely to work. Start reading help files soon... they may seem dense at first but the only way to get past that is to get started soon. Type ?data at the console to start learning about that function. Note that csv files are outside the world of standard SQL, so some of what you are doing may be very tightly linked with the particular SQL engine you are using. -- Sent from my phone. Please excuse my brevity. On October 24, 2017 7:01:36 AM PDT, Morkus via R-help <r-help at r-project.org> wrote:>Hello, > >I'm new to R so this is probably a simple question for somebody. > >I have an RScript that reads a CSV on the disk using read.table(...). >It then does a boxM test using that data. > >However, I'm now trying to load the same data via an SQL command, but I >can't seem to get the data structure defined so R will like it -- using >the included "iris" dataset. > >I've tried these ways of loading the SQL statement into a compatible R >Structure: > >irisQuery <- data(dbGetQuery(conn, "select * from iris")) > >irisQuery <- data.frame(dbGetQuery(conn, "select * from iris")) > >irisQuery <- table(dbGetQuery(conn, "select * from iris")) > >. >. >. >Followed by: > >boxM(irisQuery[,-5], irisQuery[,5]) > >Nothing works work. > >For example, if i use ... > >irisQuery <- data.frame(dbGetQuery(conn, "select * from iris")) > >I get this error from R on the boxM test: Error: is.numeric(x) || >is.logical(x) is not TRUE > >The SQL Statement is returning results, but their not CSV. Not sure if >that matters. >------------------- > >So, how do I read a SQL statement into an R structure like I read the >CSV using "read.table" so the boxM test will work? > >Thanks very much in advance. > >- M > >Sent from [ProtonMail](https://protonmail.com), Swiss-based encrypted >email. > [[alternative HTML version deleted]] > >______________________________________________ >R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see >https://stat.ethz.ch/mailman/listinfo/r-help >PLEASE do read the posting guide >http://www.R-project.org/posting-guide.html >and provide commented, minimal, self-contained, reproducible code.
Jeff Newmiller
2017-Oct-24 17:57 UTC
[R] Creating a data table (frame?) from a SQL Statement?
Please always cc the list... the group usually has better answers than any one person, and I don't do private consulting on the net. For future reference, there is also an r-sig-db mailing list where this question really belongs. Since I almost never use jdbc, I would need a reprex (hint you are ignoring my advice... a good way to get ignored), which this still isn't. There is probably enough here for someone more familiar than I to help you with. I for one find it hard to understand why read.table is successfully figuring out how to make sense of the presumably-valid data frame that dbGetQuery is returning. That is, you really don't need to send everything through read.table in order to make use of it. Maybe try irisDF <- dbGetQuery(conn, "select * from iris") str( irisDF ) -- Sent from my phone. Please excuse my brevity. On October 24, 2017 10:11:28 AM PDT, Morkus <morkus at protonmail.com> wrote:>Jeff, > >Excellent points, thank you! > >Yes, I have all the required packages installed. > >Below's the actual full script. > >require(Rserve) >require(biotools) >library(rJava) >library(RJDBC) >drv <- JDBC("com.mysql.jdbc.Driver","/Users/.../mysql.jar") >conn <- dbConnect(drv, "jdbc:mysql://localhost:3306/morkus","root", >"password") >dbListTables(conn) > // works! >count <- dbGetQuery(conn,"select count(*) from iris") // >works! >irisQuery <- read.table(dbGetQuery(conn, "select * from iris")) works! >irisQuery > // displays table from iris dataset I imported into MySQL >boxM(irisQuery[,-5], irisQuery[,5]) > / />>> FAILS! <<< "Error: is.numeric(x) || is.logical(x) is not TRUE" >dbDisconnect(conn) > // displays TRUE. > >---- > >Few rows of displayed data in R Console from line above: "irisQuery" > > Sepal.Length Sepal.Width Petal.Length Petal.Width Species >1 5.1 3.5 1.4 0.2 setosa >2 4.9 3 1.4 0.2 setosa >3 4.7 3.2 1.3 0.2 setosa >4 4.6 3.1 1.5 0.2 setosa >5 5 3.6 1.4 0.2 setosa >6 5.4 3.9 1.7 0.4 setosa >7 4.6 3.4 1.4 0.3 setosa >. >. >. >--- > >Now, if I do the boxM statistic reading a CSV file from disk, like >this: > >irisQuery = read.table('/Users/.../iris.csv', sep=',', header=FALSE, >stringsAsFactor=FALSE) > >Then everything works! (But I'm not reading files from a CSV on the >disk; rather, from SQL as above.) > >--- > >So my problem is that I can't seem to package the SQL Result (shown >above) so that it works like the read.table. I'm missing some R >function to transform the SQL to a "table" R can work with. > >Does that help fill in the missing pieces? > >Sorry my first posting wasn't this complete. > >Thanks again in advance, > >- M > >Sent from [ProtonMail](https://protonmail.com), Swiss-based encrypted >email. > >> -------- Original Message -------- >> Subject: Re: [R] Creating a data table (frame?) from a SQL Statement? >> Local Time: October 24, 2017 12:40 PM >> UTC Time: October 24, 2017 4:40 PM >> From: jdnewmil at dcn.davis.ca.us >> To: Morkus <morkus at protonmail.com>, r-help at r-project.org ><r-help at r-project.org> >> >> Your question desperately needs a reproducible example (a.k.a. >"reprex"), because you have to be using contributed packages to do any >of this. You also need to clarify whether you are intending to access >data already in an external database, or are planning to load it using >R and manipulate it with SQL. >> >> I suggest that you install the "reprex" and "sqldf" packages and read >their documentation and try out their examples. Then when you ask a >question use reprex to be sure we see all the steps needed to get to >where you are stuck. In particular we need the library calls that >preceeded your sample code, but the reprex package will verify that you >have it all there before you send the question. >> >> FWIW, note that the data function has a specific purpose of pulling >data sets by name out of packages into your workspace, and giving it >the output of randomly selected functions is not likely to work. Start >reading help files soon... they may seem dense at first but the only >way to get past that is to get started soon. Type >> >> ?data >> >> at the console to start learning about that function. >> >> Note that csv files are outside the world of standard SQL, so some of >what you are doing may be very tightly linked with the particular SQL >engine you are using. >> >> Sent from my phone. Please excuse my brevity. >> >> On October 24, 2017 7:01:36 AM PDT, Morkus via R-help >r-help at r-project.org wrote: >> >>> Hello, >>> I'm new to R so this is probably a simple question for somebody. >>> I have an RScript that reads a CSV on the disk using >read.table(...). >>> It then does a boxM test using that data. >>> However, I'm now trying to load the same data via an SQL command, >but I >>> can't seem to get the data structure defined so R will like it -- >using >>> the included "iris" dataset. >>> I've tried these ways of loading the SQL statement into a compatible >R >>> Structure: >>> irisQuery <- data(dbGetQuery(conn, "select * from iris")) >>> irisQuery <- data.frame(dbGetQuery(conn, "select * from iris")) >>> irisQuery <- table(dbGetQuery(conn, "select * from iris")) >>> . >>> . >>> . >>> Followed by: >>> boxM(irisQuery[,-5], irisQuery[,5]) >>> Nothing works work. >>> For example, if i use ... >>> irisQuery <- data.frame(dbGetQuery(conn, "select * from iris")) >>> I get this error from R on the boxM test: Error: is.numeric(x) || >>> is.logical(x) is not TRUE >>> >>> The SQL Statement is returning results, but their not CSV. Not sure >if >>> that matters. >>> >>> So, how do I read a SQL statement into an R structure like I read >the >>> CSV using "read.table" so the boxM test will work? >>> Thanks very much in advance. >>> >>> - M >>> >>> Sent from [ProtonMail](https://protonmail.com), Swiss-based >encrypted >>> email. >>> [[alternative HTML version deleted]] >>> --------------------------------------------------------------- >>> >>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see >>> https://stat.ethz.ch/mailman/listinfo/r-help >>> PLEASE do read the posting guide >>> http://www.R-project.org/posting-guide.html >>> and provide commented, minimal, self-contained, reproducible code.