用R查询语句“insert into”

I want to add real value calculated earlier on a postgresql database

我想在postgresql数据库中添加之前计算的实际值

Imp1=5/4
req=paste("INSERT INTO important (num_cluster,type,indicateur_imp) VALUES (1,'temperature',Imp1)")
resultat=dbGetQuery(con,req)

error

错误

Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERREUR:  la colonne Â« imp1 Â» n'existe pas
LINE 1: ...m_cluster,type,indicateur_imp) VALUES (1,'temperature',Imp1)
                                                                  ^
)
Warning message:
In postgresqlQuickSQL(conn, statement, ...) :
  Could not create executeINSERT INTO important (num_cluster,type,indicateur_imp) VALUES (1,'temperature',Imp1)

1 个解决方案

#1

I think should give you what you're looking for:

我认为应该给你你想要的东西：

Imp1 <- 32.1
req  <- paste("INSERT INTO important (num_cluster,type,indicateur_imp) 
VALUES (1,'temperature',",Imp1, ")",sep="")

That will output:

这将输出：

[1] "INSERT INTO important (num_cluster,type,indicateur_imp) VALUES (1,'temperature',32.1)"

[1]“INSERT INTO important（num_cluster，type，indicationur_imp）VALUES（1，'temperature'，32.1）”

paste() will take any variable and return it's value as a string, and you chain items together with commas. You can end the statement with a sep= parameter. To be the most literal (which I like doing for queries) I just use sep = "" and type the whole thing literally. So If I had multiple values I wanted to insert I would do something like this:

paste（）将获取任何变量并将其值作为字符串返回，并将项目与逗号链接在一起。您可以使用sep =参数结束语句。要成为最文字的（我喜欢为查询做）我只使用sep =“”并按字面输入整个事物。所以，如果我有多个值，我想插入，我会做这样的事情：

var1 <- round(rnorm(5))
var2 <- c("'temp'","'pressure'","'precip'","'volume'","'mass'")
for(i in 1:length(var1)){
req <- paste("INSERT INTO important (num_cluster,type,indicateur_imp) VALUES   
(1,",var2[i],",",var1[i],")",sep="")
# not run, but do to send your query 
# dbSendQuery(conn,req)
print(req)
}

That will send the following queries:

这将发送以下查询：

[1] "INSERT INTO important (num_cluster,type,indicateur_imp) VALUES (1,'temp',0.09)"

[1]“INSERT INTO important（num_cluster，type，indicationur_imp）VALUES（1，'temp'，0.09）”

[1] "INSERT INTO important (num_cluster,type,indicateur_imp) VALUES (1,'pressure',0.04)"

[1]“INSERT INTO important（num_cluster，type，indicationur_imp）VALUES（1，'pressure'，0.04）”

[1] "INSERT INTO important (num_cluster,type,indicateur_imp) VALUES (1,'precip',-1.2)"

[1]“INSERT INTO important（num_cluster，type，indicationur_imp）VALUES（1，'precip'， - 1.2）”

[1] "INSERT INTO important (num_cluster,type,indicateur_imp) VALUES (1,'volume',0.78)"

[1]“INSERT INTO important（num_cluster，type，indicationur_imp）VALUES（1，'volume'，0.78）”

[1] "INSERT INTO important (num_cluster,type,indicateur_imp) VALUES (1,'mass',1.15)"

[1]“INSERT INTO important（num_cluster，type，indicationur_imp）VALUES（1，'mass'，1.15）”

#1