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?

zx8754 · Accepted Answer · 2018-09-24 11:23:59Z

4

Using RSQLite package:

library(RSQLite)

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

edited Sep 24, 2018 at 11:23

answered Mar 31, 2015 at 9:20

zx8754

56.7k12 gold badges131 silver badges229 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Read and query SQLite database using sqldf

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related