Reference a of list values in R in a sql chunk

Question

I want to reference a list of values to be used in a sql chunk.

I have tried using the dplyr::translate_sql function to convert the list to a correct syntax, but it doesn't seem to be correct. I can copy and paste the output of the translate_sql object and it will run, but I cannot embed thoe object.

values <- translate_sql(c("value1", "value2", "value3"))

print(values)

<SQL> ('value1', 'value2', 'value3')

select *
from db.table
where column in ?values

I get an error message saying the syntax is incorrect.

However if I just copy and paste the values (See code below, it runs perfectly)

select *
from db.table
where column in ('value1', 'value2', 'value3')

Incorrect syntax near '('VALUE001', 'VALUE002', 'VALUE003', 'VALUE004', 'VALUE007', 'VALUE008', 'VALUE009', 'VALUE011', 'VALUE012', 'VALUE013')' Also, I am trying the run the query in a sql chunk in Rmarkdown — Henry
– Henry, Commented Sep 10, 2019 at 22:53
This could be helpful: dbplyr.tidyverse.org/articles/sql-translation.html — Yifu Yan
– Yifu Yan, Commented Sep 10, 2019 at 22:53
Thanks for the link. I have already gone through the page previously. — Henry
– Henry, Commented Sep 10, 2019 at 22:55

magster · Accepted Answer · 2020-09-30 18:28:40Z

2

After much pain, this blog solved this problem for me: https://irene.rbind.io/post/using-sql-in-rstudio/#passing-variables-tofrom-sql-chunks "Adding an * to the end of the variable collapses the vector into a single comma-separated expression, rather than outputting a vector of SQL expressions."

values<-glue::glue_sql("{myrvariable*}", .con = con)
SELECT * FROM yourtable WHERE column IN (?values)

answered Sep 30, 2020 at 18:28

magster

212 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Exelian Over a year ago

It's helpful if you include a small summary of the resource you're linking in the answer. This makes it searchable and prevents dead links.

boshek · Accepted Answer · 2019-09-10 23:01:58Z

0

A handy function for this is glue_sql which works like this:

library(glue)
library(dbplyr)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
colnames(iris) <- gsub("[.]", "_", tolower(colnames(iris)))
DBI::dbWriteTable(con, "iris", iris)

values <- translate_sql(c("value1", "value2", "value3"))

glue_sql("select *
         from db.table
         where column in {values}",
         .con = con)
#> <SQL> select *
#> from db.table
#> where column in '(''value1'', ''value2'', ''value3'')'

^{Created on 2019-09-10 by the reprex package (v0.3.0)}

answered Sep 10, 2019 at 23:01

boshek

4,4462 gold badges36 silver badges68 bronze badges

3 Comments

Henry Over a year ago

I gave this a try and am unfortunately still getting the same syntax error.

boshek Over a year ago

Can you be more specific?

Henry Over a year ago

Incorrect syntax near '(' 'VALUE001', 'VALUE002', 'VALUE003' ')'

Yifu Yan · Accepted Answer · 2019-09-10 23:24:57Z

0

If you want to run the query as a string:

sql <- translate_sql(c("value1", "value2", "value3"))

query <- paste(
    "select *
from db.table
where column in",
    as.character(sql)
)

# or

glue::glue("select *
from db.table
where column in {as.character(sql)}")

It will be translated into

>writeLines(query)
select *
from db.table
where column in ('value1', 'value2', 'value3')

edited Sep 10, 2019 at 23:24

answered Sep 10, 2019 at 22:58

Yifu Yan

6,1561 gold badge21 silver badges37 bronze badges

6 Comments

Henry Over a year ago

This works but is it possible to pass the vector directly into the query? Rather than select * from db.table where column in ('value1', 'value2', 'value3') Pass the vector directly into the query. select * from db.table where column in ?vector_name

Yifu Yan Over a year ago

Just modified my answer, by using glue, it's similar to boshek's answer.

Henry Over a year ago

Again, this works as it creates a sql string that I can run. However, the code gets messy if I need to repeat it for multiple columns. column in ('value1', 'value2', 'value3') or column2 in ('value1', 'value2', 'value3') etc. Using the sql code chunk in R markdown, is it possible to pass a list into it so the code isn't as messy? ``` {sql query, connection = conn, output.var = 'df'} select * from db.table where (column1 in ?values or column2 in ?values or column3 in ?values) ```

Yifu Yan Over a year ago

Yes, use glue function in glue package where you can have placeholder variables inside {}. Please see boshek's and my answer.

Henry Over a year ago

I see what you mean, but when I try to run the query using glue, i still get an incorrect syntax error.

|

Collectives™ on Stack Overflow

Reference a of list values in R in a sql chunk

3 Answers 3

1 Comment

3 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related