CODEWITHSUNDEEP
mysqlrrmysql
Ravinder Kumar
Ravinder Kumar

Reputation: 57

How read few columns of a table using dbReadTable

I am using the below code after creating my connection, "mydb" with my MySQL server to import the data into R and it's working fine.

my_data <- dbReadTable(mydb, "ar_data")

But I don't want to import or read the whole table, I just don't want to read first 5 columns. How can I do that ?

Upvotes: 1

Views: 1465

Answers (1)

lukeA
lukeA

Reputation: 54257

Maybe try dbSendQuery: 

library(DBI)
library(RMySQL) 
drv <- dbDriver("MySQL") 
con <- dbConnect (drv, dbname="mydb", user="username") 
dbWriteTable(con, "mtcars", mtcars)
dbReadTable(con, "mtcars") # full table

sql <- paste0("SELECT ", paste(dbListFields(con, "mtcars")[-(1:5)], collapse=","), " FROM mtcars LIMIT 5")
res <- dbSendQuery(con, sql)
dbFetch(res)
#   drat    wt  qsec vs am gear carb
# 1 3.90 2.620 16.46  0  1    4    4
# 2 3.90 2.875 17.02  0  1    4    4
# 3 3.85 2.320 18.61  1  1    4    1
# 4 3.08 3.215 19.44  1  0    3    1
# 5 3.15 3.440 17.02  0  0    3    2
dbClearResult(res)

res <- dbSendQuery(con, 'DROP TABLE mtcars')
dbDisconnect(con)

Upvotes: 2

Related Questions