如何将存储在r变量中的值传递到R中的PostgreSQL查询的where子句中的列 [英] how to pass value stored in r variable to a column in where clause of postgresql query in R
本文介绍了如何将存储在r变量中的值传递到R中的PostgreSQL查询的where子句中的列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在RStudio中使用RPostgresql和DBI.
I am using RPostgresql and DBI in RStudio.
library(RPostgreSQL)
library(DBI)
#save password
prod_pw <- {
"my_pass"
}
# make db connection
con <- dbConnect(RPostgreSQL::PostgreSQL(), dbname = 'my_dbname',
host = 'my_host',
port = 5432, # or any other port
user = 'user_name',
password = prod_pw)
# save query
myquery<- 'select count(*), state from results where date=\'2018-11-10\';'
#run query
my_query_stats<-dbGetQuery(con,myquery)
但是我想使它自动化
可以从用户输入日期,也可以至少在运行脚本时使用系统日期.
the date can be either input from the user, or at minimum use the system date at the time of running the script.
我尝试过的方法: 例如:
What I tried: ex:
this_date<-Sys.Date()
#or accept from user
this_date<- readline("Please Enter Date\n")
# Please Enter Date2018-11-30
# this_date
# [1] "2018-11-30"
myquery<- 'select count(*), state from results where date=this_date;'
dbGetQuery(con,myquery) # didn't work, null value returned.
myquery<- 'select count(*), state from results where date=\'this_date\';'
dbGetQuery(con,myquery) # didn't work, null value returned.
myquery<- 'select count(*), state from results where date=\"this_date\";'
dbGetQuery(con,myquery) # didn't work, returned null value.
请告知如何接受用户的值并将其发送到psql查询的日期字段.
Please advise on how to accept value from user and send that to the psql query's date field.
推荐答案
尝试一下
this_date = "2018-11-30"
string = paste("select count(*), state from results where date=
TO_DATE(this_date,'YYYYMMDD')")
rs = dbGetQuery(connection,string)
这篇关于如何将存储在r变量中的值传递到R中的PostgreSQL查询的where子句中的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文