CODEWITHSUNDEEP
rmysql-connector
SherlockLTS
SherlockLTS

Reputation: 165

How to store SQL query result into data frame in R

I want to store SQL query into data frame for visualization since it's difficult to drawing plots from SQL query result directly. below is my R code so far:

mydb = dbConnect(MySQL(),
                 user='root',
                 password='XXXXX',
                 dbname='Databasename',
                 port=3306)
dbListTables(mydb)

rs <- dbSendQuery(mydb, "SELECT XXX FROM XXX WHERE X = Y;") 
dbFetch(rs)

How can I store rs to a data frame using R? Obviously it can't be converted uisng the data.frame function directly.

Upvotes: 1

Views: 4377

Answers (1)

akrun
akrun

Reputation: 886938

We can assign the dbFetch output to an object and then clear the results with dbClearResult

rs1 <- dbFetch(rs)
dbClearResult(rs)

Now, check

str(rs1)

Or instead of dbSendQuery/dbFetch, use dbGetQuery

rs <- dbGetQuery(mydb, "SELECT XXX FROM XXX WHERE X = Y;")

Using a reproducible example

library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars)
rs <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
rs1 <- dbFetch(rs)
dbClearResult(rs)
dbDisconnect(con)

Upvotes: 1

Related Questions