在通过 ODBC 连接到数据库的 R 中，如何在运行一个查询后断开连接以发送另一个查询而不取消前一个查询? [英] In R connected to a database through ODBC, how do I disconnect after running one query to send another query without cancelling the previous?

问题描述

我是 R 的新手，也是 Stack Overflow 的新手，如果我没有正确发布，请见谅！我使用 ODBC 和 DBI 包将 R 链接到 Access 数据库.我在互联网上搜索并找不到编写更新查询的方法，因此我创建了一个循环，该循环几乎可以手动执行相同的操作.问题是要做到这一点，我使用了 dbSendStatement 函数.似乎当您使用该函数时，您需要一种方法在继续执行另一个查询之前结束它，否则您会收到此错误:

I am new to R and new to Stack Overflow, so apologies if I don't post this correctly! I have R linked to an Access database using the ODBC and DBI packages. I scoured the internet and couldn't find a way to write an update query, so I've created a loop that manually pretty much does the same thing. The problem is that to do this, I am using the dbSendStatement function. It seems that when you use that function you need a way to then end it before moving onto another query, or else you get this error:

In new_result(connection@ptr, statement, immediate) :
  Cancelling previous query

如何完成一个查询，以便在不取消最后一个查询的情况下继续进行下一个查询?

How do I complete one query so I can move onto the next without canceling the last?

这是我认为相关的代码部分:

Here is the part of my code I think is relevant:

require(DBI)
require (odbc)

dB.Connection = dbConnect(odbc::odbc(), dsn = "Development Database")

BLDG.LVL.Details = data.frame()

for (i in 2:length(Temp.File.List)){
  Temp.BLDG.LVL = read_excel(Temp.File.List[i], range = cell_cols("A:O"))
  BLDG.LVL.Details = rbind(BLDG.LVL.Details, Temp.BLDG.LVL)
  file.move(Temp.File.List[i],paste("C:/UserData/",Sys.getenv("USERNAME"),"/OneDrive - Siemens AG/Development Database/06 FIM (BLDG LVL)/Archive", sep = ""))}

for (i in 1:nrow(BLDG.LVL.Details)){
  DBI::dbSendStatement(conn = dB.Connection, statement = paste("UPDATE DC_FIMs_BLDG_Lvl SET Included = ",BLDG.LVL.Details[i,3]," WHERE FIM_ID = ",BLDG.LVL.Details[i,1]," AND BUILDING_ID = ",BLDG.LVL.Details[i,2],";", sep = ""))
  DBI::dbSendStatement(conn = dB.Connection, statement = paste("UPDATE DC_FIMs_BLDG_Lvl SET Proposed_kW_Yr1 = ",BLDG.LVL.Details[i,4]," WHERE FIM_ID = ",BLDG.LVL.Details[i,1]," AND BUILDING_ID = ",BLDG.LVL.Details[i,2],";", sep = ""))
  DBI::dbSendStatement(conn = dB.Connection, statement = paste("UPDATE DC_FIMs_BLDG_Lvl SET Proposed_kWh_Yr1 = ",BLDG.LVL.Details[i,6]," WHERE FIM_ID = ",BLDG.LVL.Details[i,1]," AND BUILDING_ID = ",BLDG.LVL.Details[i,2],";", sep = ""))
  DBI::dbSendStatement(conn = dB.Connection, statement = paste("UPDATE DC_FIMs_BLDG_Lvl SET Proposed_NGas_Yr1 = ",BLDG.LVL.Details[i,7]," WHERE FIM_ID = ",BLDG.LVL.Details[i,1]," AND BUILDING_ID = ",BLDG.LVL.Details[i,2],";", sep = ""))
  DBI::dbSendStatement(conn = dB.Connection, statement = paste("UPDATE DC_FIMs_BLDG_Lvl SET Proposed_H2O_Yr1 = ",BLDG.LVL.Details[i,8]," WHERE FIM_ID = ",BLDG.LVL.Details[i,1]," AND BUILDING_ID = ",BLDG.LVL.Details[i,2],";", sep = ""))
  DBI::dbSendStatement(conn = dB.Connection, statement = paste("UPDATE DC_FIMs_BLDG_Lvl SET Proposed_FuelOil_Yr1 = ",BLDG.LVL.Details[i,9]," WHERE FIM_ID = ",BLDG.LVL.Details[i,1]," AND BUILDING_ID = ",BLDG.LVL.Details[i,2],";", sep = ""))
  DBI::dbSendStatement(conn = dB.Connection, statement = paste("UPDATE DC_FIMs_BLDG_Lvl SET Proposed_Propane_Yr1 = ",BLDG.LVL.Details[i,10]," WHERE FIM_ID = ",BLDG.LVL.Details[i,1]," AND BUILDING_ID = ",BLDG.LVL.Details[i,2],";", sep = ""))
  DBI::dbSendStatement(conn = dB.Connection, statement = paste("UPDATE DC_FIMs_BLDG_Lvl SET Proposed_Other_Yr1 = ",BLDG.LVL.Details[i,11]," WHERE FIM_ID = ",BLDG.LVL.Details[i,1]," AND BUILDING_ID = ",BLDG.LVL.Details[i,2],";", sep = ""))
  DBI::dbSendStatement(conn = dB.Connection, statement = paste("UPDATE DC_FIMs_BLDG_Lvl SET Operational_Savings_USD_BLDG = ",BLDG.LVL.Details[i,13]," WHERE FIM_ID = ",BLDG.LVL.Details[i,1]," AND BUILDING_ID = ",BLDG.LVL.Details[i,2],";", sep = ""))
  DBI::dbSendStatement(conn = dB.Connection, statement = paste("UPDATE DC_FIMs_BLDG_Lvl SET Operational_Savings_Yrs_BLDG = ",BLDG.LVL.Details[i,14]," WHERE FIM_ID = ",BLDG.LVL.Details[i,1]," AND BUILDING_ID = ",BLDG.LVL.Details[i,2],";", sep = ""))
  DBI::dbSendStatement(conn = dB.Connection, statement = paste("UPDATE DC_FIMs_BLDG_Lvl SET Operational_Savings_Persistence_BLDG = ",BLDG.LVL.Details[i,15]," WHERE FIM_ID = ",BLDG.LVL.Details[i,1]," AND BUILDING_ID = ",BLDG.LVL.Details[i,2],";", sep = ""))
  DBI::dbSendStatement(conn = dB.Connection, statement = paste("UPDATE DC_FIMs_BLDG_Lvl SET Included = ",BLDG.LVL.Details[i,3]," WHERE FIM_ID = ",BLDG.LVL.Details[i,1]," AND BUILDING_ID = ",BLDG.LVL.Details[i,2],";", sep = ""))}

推荐答案

dbSendStatement() 返回一个结果对象，需要使用 dbClearResult() 清除它.使用 dbExecute() 发送查询更容易，基本上这是 dbSendStatement() 和 dbClearResult() 的组合.

dbSendStatement() returns a result object, you need to clear it with dbClearResult(). It's easier to send the query with dbExecute() instead, basically this is a combination of dbSendStatement() and dbClearResult().

for (i in 1:nrow(BLDG.LVL.Details)){
  DBI::dbExecute(conn = dB.Connection, statement = paste("UPDATE DC_FIMs_BLDG_Lvl SET Included = ",BLDG.LVL.Details[i,3]," WHERE FIM_ID = ",BLDG.LVL.Details[i,1]," AND BUILDING_ID = ",BLDG.LVL.Details[i,2],";", sep = ""))
  DBI::dbExecute(conn = dB.Connection, statement = paste("UPDATE DC_FIMs_BLDG_Lvl SET Proposed_kW_Yr1 = ",BLDG.LVL.Details[i,4]," WHERE FIM_ID = ",BLDG.LVL.Details[i,1]," AND BUILDING_ID = ",BLDG.LVL.Details[i,2],";", sep = ""))

有几种方法可以改善这一点:

There are a few ways to improve this:

• 使用 dbQuoteLiteral() 引用文字值并防止 SQL 注入，这也解决了 在通过 ODBC 连接到 Access 数据库的 R 中，如何更新文本字段?:

• Use dbQuoteLiteral() to quote literal values and protect against SQL injection, this also solves In R connected to an Access Database through ODBC, how can I update a text field?:

for (i in 1:nrow(BLDG.LVL.Details)){
  DBI::dbExecute(conn = dB.Connection, statement = paste("UPDATE DC_FIMs_BLDG_Lvl SET Included = ",DBI::dbQuoteLiteral(conn,BLDG.LVL.Details[i,3])," WHERE FIM_ID = ",DBI::dbQuoteLiteral(conn,BLDG.LVL.Details[i,1])," AND BUILDING_ID = ",DBI::dbQuoteLiteral(conn,BLDG.LVL.Details[i,2]),";", sep = ""))
  DBI::dbExecute(conn = dB.Connection, statement = paste("UPDATE DC_FIMs_BLDG_Lvl SET Proposed_kW_Yr1 = ",DBI::dbQuoteLiteral(conn,BLDG.LVL.Details[i,4])," WHERE FIM_ID = ",DBI::dbQuoteLiteral(conn,BLDG.LVL.Details[i,1])," AND BUILDING_ID = ",DBI::dbQuoteLiteral(conn,BLDG.LVL.Details[i,2]),";", sep = ""))

使用参数来安全地传递查询参数(不幸的是，占位符语法因 DBMS 而异，? 可能会起作用):

for (i in 1:nrow(BLDG.LVL.Details)){
  DBI::dbExecute(conn = dB.Connection, statement = "UPDATE DC_FIMs_BLDG_Lvl SET Included = ? WHERE FIM_ID = ? AND BUILDING_ID = ?;", params = list(BLDG.LVL.Details[i,3],BLDG.LVL.Details[i,1],BLDG.LVL.Details[i,2]))
  ...

冒着风险声明，在同一个查询中更新多列会更快，无论是否有占位符:

Risking to state the obvious, updating more than one column in the same query will be much faster, with or without placeholders:

for (i in 1:nrow(BLDG.LVL.Details)){
  DBI::dbExecute(conn = dB.Connection, statement = "UPDATE DC_FIMs_BLDG_Lvl SET Included = ?, Proposed_kW_Yr1 = ? WHERE FIM_ID = ? AND BUILDING_ID = ?;", params = list(BLDG.LVL.Details[i,3],BLDG.LVL.Details[i,4],BLDG.LVL.Details[i,1],BLDG.LVL.Details[i,2]))
  ...

我相信您可以用以下形式替换整个循环:

I believe you can replace your entire loop with something of the form:

library(dm)

...

tbl <- dplyr::tbl(conn, "DC_FIMs_BLDG_Lvl")
dplyr::rows_update(tbl, BLDG.LVL.Details, by = c("FIM_ID", "BUILDING_ID"), in_place = TRUE, copy = TRUE)

该功能需要加载 dm 包.在 https://krlmlr.github.io/dm/articles 阅读更多/howto-dm-rows.html -- 该文本显示了一种不同的方法，如果您正在访问数据库上的多个表，该方法很有用.让我知道它是否适合您.

The functionality requires the dm package to be loaded. Read more at https://krlmlr.github.io/dm/articles/howto-dm-rows.html -- that text shows a different approach that is useful if you are accessing multiple tables on a database. Let me know if it works for you.

查看用于发送更新查询的 dbx 包，它是否适用于你呢?

Take a look at the dbx package for sending update queries, does it work for you?

这篇关于在通过 ODBC 连接到数据库的 R 中，如何在运行一个查询后断开连接以发送另一个查询而不取消前一个查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！