Dynamic SQL queries from dataframe containing statements

[budugulo]

Suppose I have the following data frame, which contains SQL statements.

# A tibble: 2 x 1
  queries                                                            
  <chr>                                                              
1 SELECT * FROM table1 WHERE g in ('abc') and id in ('2099343390037')
2 SELECT * FROM table1 WHERE g in ('xyz') and id in ('503343390037') 

For each query, I would like to create a data frame and then bind them all. How can I do it without manually specifying?

library(dplyr)
library(DBI)
library(RSQLite)


# Toy Database------------------------------------------------------------------
table1 <- tibble(
  g = c("abc", "abc", "xyz", "a", "c"),
  id = c(2099343390037, 100, 503343390037, 2, 100)
)
# in-memory SQLite database and copy over table1
con_db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(con_db, table1)

# Dataframe SQL statements -----------------------------------------------------
q1 <- "SELECT * FROM table1 WHERE g in ('abc') and id in ('2099343390037')"
q2 <- "SELECT * FROM table1 WHERE g in ('xyz') and id in ('503343390037')"
df <- tibble(queries = c(q1, q2))
df
#> # A tibble: 2 x 1
#>   queries                                                            
#>   <chr>                                                              
#> 1 SELECT * FROM table1 WHERE g in ('abc') and id in ('2099343390037')
#> 2 SELECT * FROM table1 WHERE g in ('xyz') and id in ('503343390037')

# I can do it manually ---------------------------------------------------------
# For example:

# query 1
query1 <- df %>% slice(1) %>% pull()
df1 <- DBI::dbGetQuery(
  con_db,
  query1)
df1
#>     g           id
#> 1 abc 2.099343e+12

# query 2
query2 <- df %>% slice(2) %>% pull()
df2 <- DBI::dbGetQuery(
  con_db,
  query2)
df2
#>     g           id
#> 1 xyz 503343390037

# bind rows
bind_rows(df1, df2)
#>     g           id
#> 1 abc 2.099343e+12
#> 2 xyz 5.033434e+11

[andresrcs]

You can use purrr::map_dfr() function

library(dplyr)
library(purrr)
library(DBI)
library(RSQLite)

# Toy Database------------------------------------------------------------------
table1 <- tibble(
    g = c("abc", "abc", "xyz", "a", "c"),
    id = c(2099343390037, 100, 503343390037, 2, 100)
)
# in-memory SQLite database and copy over table1
con_db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(con_db, table1)

# Dataframe SQL statements -----------------------------------------------------
q1 <- "SELECT * FROM table1 WHERE g in ('abc') and id in ('2099343390037')"
q2 <- "SELECT * FROM table1 WHERE g in ('xyz') and id in ('503343390037')"
df <- tibble(queries = c(q1, q2))

df %>%
    map_dfr(.x = .$queries,
            .f = ~ dbGetQuery(con_db, .x))
#>     g           id
#> 1 abc 2.099343e+12
#> 2 xyz 5.033434e+11

^{Created on 2022-02-04 by the reprex package (v2.0.1)}

[budugulo]

@andresrcs Many thanks!