Hi, you can do it like this:
library(tidyverse)
# This is basically the SQL injection
clean_sql <- function (mysql, type = '99', min_width = '99', max_width = '99') {
sqlcmd <- paste(mysql, collapse=" ")
sqlcmd <- gsub('\t',replacement = ' ', sqlcmd)
sqlcmd <- gsub("type", type, sqlcmd)
sqlcmd <- gsub("min_width", min_width, sqlcmd)
sqlcmd <- gsub("max_width", max_width, sqlcmd)
sqlcmd
}
# First we build up a control table because we want to create many SQLs
iris_db <- iris
ctrl_table <- tibble(index = seq(1,3),
type = c('setosa', 'versicolor', 'versicolor'),
min_width = c(1.4, 1.1, 2.5),
max_width = c(3.4, 3.1, 3.5))
# The SQL is created here for demonstration but in reality its about 100 lines long
# It is also read in from the hard drive
strSQL <- " Select * FROM iris_db WHERE Species = 'type' AND Sepal.Width >= min_width AND Sepal.Width <= max_width"
sql_calc <- ''
# I want to create 3 SQLS based on the control table above where we inject the variables into the SQL
# Ideally i would like to know how to do this with purrr:pmap
for (i in ctrl_table$index) {
sql_calc[i] <- clean_sql(strSQL,
type = ctrl_table$type[i],
min_width = ctrl_table$min_width[i],
max_width = ctrl_table$max_width[i])
}
ctrl_table %>%
dplyr::mutate(sql = purrr::pmap_chr(list(type, min_width, max_width), clean_sql, mysql = strSQL))
#> # A tibble: 3 x 5
#> index type min_width max_width sql
#> <int> <chr> <dbl> <dbl> <chr>
#> 1 1 setosa 1.4 3.4 " Select * FROM iris_db WHERE Specie…
#> 2 2 versico… 1.1 3.1 " Select * FROM iris_db WHERE Specie…
#> 3 3 versico… 2.5 3.5 " Select * FROM iris_db WHERE Specie…
Created on 2018-08-21 by the reprex package (v0.2.0).