Execute iterative SQL queries from R using purrr functions

iterative_DBI_pulls.R

library(DBI)
library(ODBC)
library(purrr)

.con <- dbConnect(odbc(),
                  Driver = "MySQL ODBC 8.0 ANSI Driver",
                  Server = x,
                  Database = y,
                  Port = 3306, 
                  uid = u,
                  pwd = p)

# Example: iterate over tables to pull site-specific data from each table

tables <- as.list(dbListTables(.con))

# Option 1 - direct string substitution using glue::glue (not as safe, but intiuitive)
pull <- function(sql_table){
  
  dbGetQuery(.con, glue::glue("select * from {sql_table} where site = 'Cornell' limit 2"))
  
}
data1 <- map(tables, pull) %>% set_names(tables)

# Option 2 - use sqlInterpolate for string substitution and separate query building, which is a bit safer
query2 <- map(.x = map(tables, ~dbQuoteIdentifier(.con, .x)),  # dbQuoteIdentifier allows substitution of unquoted SQL parameters
              ~sqlInterpolate(.con,
                              "select * from ?table where site = 'Cornell' limit 2",
                              table = .x))
data2 <- map(.x = query2, ~dbGetQuery(.con, .x)) %>% set_names(tables)