CODEWITHSUNDEEP
rdplyrrsqlite
Ignacio
Ignacio

Reputation: 7928

read only some columns with RSQLite?

I only want to read 3 columns from a table. Right now I'm doing something that I guess is very inefficient 

library(dplyr, quietly = T, warn.conflicts = F)
library(RSQLite, quietly = T) 
db <- dbConnect( SQLite() , "SIPP08.db" )
dbListTables(db) 
w13 <- dbReadTable( db , 'w13' )
dbDisconnect(db)

df = w13 %>%
  select(ssuid, epppnum,ejbind1)

Is there a better way of doing this?

Thanks!

I also tried using dplyr like this

my_db <- src_sqlite(path = "SIPP08.db")
w13 <- tbl(my_db, "w13")
df = w13 %>%
  select(ssuid, epppnum,ejbind1)

But it does not work

Upvotes: 0

Views: 731

Answers (1)

dickoa
dickoa

Reputation: 18437

It would be better to directly query the SQLite DB, something along these lines

library(dplyr)
library(RSQLite)

db <- src_sqlite("SIPP08.db")
df <- tbl(db, sql("SELECT ssuid, epppnum, ejbind1 from wt13"))

I don't why you have a list but you can just use the DBI package and RSQLite to have a data.frame

library(DBI)
db <- dbConnect(SQLite() , "SIPP08.db")
df <- dbGetQuery(db, "SELECT ssuid, epppnum, ejbind1 from wt13")

I don't have your data but the first code works for me using a toy dataset.

Upvotes: 3

Related Questions