2016-11-12

Setting up for R to use survey.db

Here's a short R program that selects latitudes and longitudes from an SQLite database stored in a file called survey.db:

library(RSQLite)
## Loading required package: DBI
connection <- dbConnect(SQLite(), "survey.db")
results <- dbGetQuery(connection, "SELECT Site.lat, Site.long FROM Site;")
print(results)
##      lat    long
## 1 -49.85 -128.57
## 2 -47.15 -126.72
## 3 -48.87 -123.40
dbDisconnect(connection)
## [1] TRUE

Query survey.db in R

library(RSQLite)

connection <- dbConnect(SQLite(), "survey.db")

getName <- function(personID) {
  query <- paste0("SELECT personal || ' ' || family FROM Person WHERE id =='", personID, "';")
  return(dbGetQuery(connection, query))
}

print(paste("full name for dyer:", getName('dyer')))
## [1] "full name for dyer: William Dyer"
dbDisconnect(connection)
## [1] TRUE

Database helper functions in R

R's database interface packages (like RSQLite) all share a common set of helper functions useful for exploring databases and reading/writing entire tables at once.

  • To view all tables in a database, we can use dbListTables():
connection <- dbConnect(SQLite(), "survey.db")
dbListTables(connection)
## [1] "Person"  "Site"    "Survey"  "Visited" "iris"

Database helper functions in R … cont …

  • To view all column names of a table, use dbListFields():
dbListFields(connection, "Survey")
## [1] "taken"   "person"  "quant"   "reading"

Database helper functions in R … cont …

  • To read an entire table as a dataframe, use dbReadTable():
dbReadTable(connection, "Person")
##         id  personal   family
## 1     dyer   William     Dyer
## 2       pb     Frank  Pabodie
## 3     lake  Anderson     Lake
## 4      roe Valentina  Roerich
## 5 danforth     Frank Danforth