Glue string adds double quotations or backslashes which throws error in database query-CodePudding

How do I return a string with only single quotes from the glue::glue_sql command? I have tried two different approaches:

Approach 1

con <- RAthena::dbConnect(RAthena::athena(),
                 s3_staging_dir = s3_staging_dir)

column_values <- c("thing1","thing2","thing3")

query1 <- glue::glue_sql("SELECT COUNT (*) as venues FROM schema.table WHERE column IN ({column_values*})", .con = con)
query1

This returns:

'SELECT COUNT (*) as venues FROM schema.table WHERE column IN (\'thing1\', \'thing2\', \'thing3\')'

Approach 2

con <- RAthena::dbConnect(RAthena::athena(),
                 s3_staging_dir = s3_staging_dir)

column_values <- c("thing1","thing2","thing3")

query2 <- glue::glue_sql("SELECT COUNT (*) as venues FROM schema.table WHERE column IN ({`column_values`*})", .con = con)
query2

this returns:

'SELECT COUNT (*) as venues FROM schema.table WHERE column IN ("thing1", "thing2", "thing3")'

My desired output

"SELECT COUNT (*) as venues FROM schema.table WHERE column IN ('thing1', 'thing2', 'thing3')"

CodePudding user response：

Name your vector:

col_val <- c(t1 = "thing1", t2 = "thing2", t3 = "thing2")

Then use the {vars*} syntax:

query3 <- glue::glue_sql("SELECT COUNT (*) as venues FROM schema.table WHERE column IN ({col_val*})", .con = con)
query3 

<SQL> SELECT COUNT (*) as venues FROM schema.table WHERE column IN ('thing1', 'thing2', 'thing2')

CodePudding user response：

Okay the real issue was I am using R in jupyterlab. All I needed to do was to print() the string and the backslashes were removed.

Similar stacks answer: python string adding backslash before single quotes

con <- RAthena::dbConnect(RAthena::athena(),
                 s3_staging_dir = s3_staging_dir)

column_values <- c("thing1","thing2","thing3")

query1 <- glue::glue_sql("SELECT COUNT (*) as venues FROM schema.table WHERE column IN ({column_values*})", .con = con)
print(query1)

returns:

<SQL> SELECT COUNT (*) as venues FROM schema.table WHERE column IN ('thing1', 'thing2', 'thing3')