Remember to

  • download portal_mammals.sqlite.
  • open portal_mammals.sqlite in SQLite Manager.
  • make sure the copy you are going to use in class does not have the SpeciesCounts table or view.
  • We’ve already seen how to connect to databases using dplyr.
  • What if we want to work outside of a dplyr context?
  • Use DBI and RSQLite.
    • also other database management systems plugins

Connect

library(DBI)
library(RSQLite)
portalDB <- "portal_mammals.sqlite"
conn <- dbConnect(SQLite(), portalDB)

Check out database structure

dbListTables(conn)
dbListFields(conn, "plots")
dbListFields(conn, "surveys")

Run queries

query <- "SELECT genus, species, COUNT(*)
          FROM surveys JOIN species
          ON surveys.species_ID = species.species_ID
          GROUP BY genus, species;"
species_counts <- dbGetQuery(conn, query)

Do Exercise 1 - Connect and Query.

Write new information to database

Show the original portal_mammals.sqlite in SQLite Manager.

  • Write as a table
dbWriteTable(conn, "SpeciesCounts", species_counts)
dbListTables(conn)

Show SpeciesCounts table in portal_mammals.sqlite.

  • Write as a view
viewquery <- paste("CREATE VIEW SpeciesCounts AS", query)
dbSendQuery(conn, viewquery)

Show the SpeciesCounts view in portal_mammals.sqlite.

Do Exercise 3 - Export to Database.

Assign remaining exercises.