Search code examples
rsqlitedplyrdbplyr

Select specific rows from sqlite database using dbplyr in R

I want to select specific rows (lets say row number 1, 8 and 20) from a table in sqlite database using dbplyr /dplyr package in R, but without loading whole table in memory. Can anyone help here?

Solution

  • filter would work with sqlitedb

    library(dplyr)
con <- DBI::dbConnect(RSQLite::SQLite(), dbname = ":memory:")
copy_to(con, iris, "iris")
iris_db <- tbl(con, "iris")
iris_db %>% 
     filter(row_number() %in% c(1, 8, 20))
# Source:   lazy query [?? x 5]
# Database: sqlite 3.29.0 [:memory:]
#  Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#         <dbl>       <dbl>        <dbl>       <dbl> <chr>  
#1          5.1         3.5          1.4         0.2 setosa 
#2          5           3.4          1.5         0.2 setosa 
#3          5.1         3.8          1.5         0.3 setosa