Read and query SQLite database using sqldf

Question

I have a SQLite database pisa06.db created from a data frame. I would like to read variables (columns) from this database file selectively without actually loading into memory. I know I can do it with a data frame already in the workspace. I couldn't figured out how to do it with a database in the disk without reading the whole database.

Let me give you more detail. I have a data frame pisa06. I created a database file pisa06.db in the working directory with

library(sqldf)
drv <- dbDriver("SQLite") 
con <- dbConnect(drv, "pisa06.db") 
dbWriteTable(con, "pisa06", pisa06)

Now I need to reach this database file and read some variables into a data frame without reading the whole database. I have tried many alternatives. This will not work:

df <- sqldf("select CNT, SCHOOLID from pisa06", drv="SQLite")

How can I use sqldffor this purpose? Is this the best way to do it?

Answer 1

Using RSQLite package:

library(RSQLite)

con <- dbConnect(SQLite(), dbname = "pisa06.db")
dbGetQuery(con,"select CNT, SCHOOLID from pisa06")