# RSQLITE

install.packages("RSQLite")
library(RSQLite)

# A sample sqlite database, named datasetDb, is bundled with the package, and contains all data frames in the datasets package.  See documentation.
db=RSQLite::datasetsDb()
dbListTables(db)

# connecting/using existing files
system("ls *.db", show=TRUE)

#sqlite=dbDriver("sqlite3")
sqlite = dbDriver("SQLite")
sqlite

foodsdb = dbConnect(sqlite,"foods.db")
foodsdb
dbListTables(foodsdb)
# "episodes"   "food_types"   "foods"   "foods_episodes"
dbListFields(foodsdb,"episodes")
# "id"	   "season"   "name" 

# Fetch all query results into a data frame:
episodesdf = dbGetQuery(foodsdb, "select * from episodes")
class(episodesdf)
dim(episodesdf)

# Alternatively, you can fetch queries in batches 
res = dbSendQuery(foodsdb, "select * from episodes")
dbGetStatement(res)
# "select * from episodes"
dbGetRowCount(res)
# 181
# To extract data in chunks of 10 rows (di's are also data frames)
d1 = dbFetch(res, n = 10) 
# Next 10 rows 
d2 = dbFetch(res, 10) 
# extract all remaining data
d3 = dbFetch(res,  -1) 
# instead, fetch all at once
d=dbFetch(res)

dbHasCompleted(res)
dbClearResult(res)

# clean up
dbDisconnect(foodsdb)