Using SQLite in R

September 16, 2014

(This article was first published on Digital Hardcore » rbloggers, and kindly contributed to R-bloggers)

Working on big data requires a clean and robust approach on storing and accessing the data. SQLite is an all inclusive server-less database system in a single file. This is very convenient for data exchange between colleagues. Here is a workflow of SQLite data accessing and data storing in R.

Connect to an SQLite database file and get a table directly to a data.frame data-type. Useful when handling big chunks of data or when analyzing subsets of data which can be retrieved via an SQLite query.

Source code    
  1. library("RSQLite")
  2. # connect to the sqlite file
  3. con = dbConnect(drv="SQLite", dbname="country.sqlite")
  4. # get a list of all tables
  5. alltables = dbListTables(con)
  6. # get the populationtable as a data.frame
  7. p1 = dbGetQuery( con,'select * from populationtable' )
  8. # count the areas in the SQLite table
  9. p2 = dbGetQuery( con,'select count(*) from areastable' )
  10. # find entries of the DB from the last week
  11. p3 = dbGetQuery(con, "SELECT population WHERE DATE(timeStamp) < DATE('now', 'weekday 0', '-7 days')")
  12. #Clear the results of the last query
  13. dbClearResult(p3)
  14. #Select population with managerial type of job
  15. p4 = dbGetQuery(con, "select * from populationtable where jobdescription like '%manager%'")

To leave a comment for the author, please follow the link and comment on their blog: Digital Hardcore » rbloggers. offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...

If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.


Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)