Importing SQLite DB into R
I’ve got data, now what?
We’ve already explained how to put data into a SQLite database. Now we want to grab a subset of that data and pull it into R where we can analayze or visualize it, or combine it with other kinds of data. It is in fact quite straightforward.
We write a script that tells R to open a connection to the database; then we tell it what’s inside that database; then we write an SQL query in R that grabs just the bits we’re interested in and passes it to a new R object; and then we shut the connection and clear the cache.
In this way, we’re never directly modifying our datastore. We’re always working on a copy of the data, which is handy because when we screw up - and we will - we can rest easy knowing the original data is still ok. The code looks like this:
# first we get the packages we need: library(DBI) library(RSQLite) # now we open the connection: con = dbConnect(SQLite(), dbname="YOUR-AMAZING-DATABASE.sqlite") # we can see what's inside, eg, what tables are in the database? alltables = dbListTables(con) alltables # write the query to get the information you want myQuery <- dbSendQuery(con, "SELECT id, owner_id, caption, taken_at_timestamp FROM unique_posts") # pass that information to an R object. The n = -1 bit means grab everything until there's nothing left to grab. Otherwise, you can specify how many rows etc. my_data <- dbFetch(myQuery, n = -1) # now that we're done, clear cache dbClearResult(myQuery) # now carry on and begin manipulating my_data # for more information see # http://tiffanytimbers.com/querying-sqlite-databases-from-r/ # also perhaps this https://www.r-bloggers.com/using-sqlite-in-r/