在 R 脚本中传递字符串变量以在 SQL 语句中使用它 [英] Pass string variable in R script to use it in SQL statement

问题描述

我尝试在 R 脚本中使用字符串变量通过 SQL 语句使用，例如:

I tried use a string variable in R script to use through SQL statement for example:

x="PASS"

SQL<- paste("select ID, NAME, STATUS from STUDENT where STATUS =(",x,")",sep="")
Q1 <- dbGetQuery(con, SQL)

错误说:

mysqlExecStatement(conn, statement, ...) 中的错误:
RS-DBI 驱动程序:(无法运行语句:'where 子句'中的未知列'PASS')

Error in mysqlExecStatement(conn, statement, ...) :
RS-DBI driver: (could not run statement: Unknown column 'PASS' in 'where clause')

这意味着 STATUS =(",x,")" = PASS 并且它必须'PASS'并加上引号''

That means STATUS =(",x,")" = PASS and it must 'PASS' with add quote ''

我尝试将 '' 放在下面，但没有成功.

I tried to put the '' but no success as the following.

SQL <- paste("select ID, NAME, STATUS from STUDENT where STATUS ='(",x,")' ",sep="")
Q1 <- dbGetQuery(con, SQL)

我用数字对其进行了测试，它运行良好，但是当我使用字符串时，它不起作用，因为该值必须在引号 ' ' 中.

I tested it with number and it is work well but when I use string it is not working, because the value must be in quotes ' '.

推荐答案

使用 sprintf 代替:

x <- "PASS"
sprintf("select ID, NAME, STATUS from STUDENT where STATUS = '%s'", x)

## [1] "select ID, NAME, STATUS from STUDENT where STATUS = 'PASS'"

这篇关于在 R 脚本中传递字符串变量以在 SQL 语句中使用它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！