如何使用R DBI传递data.frame进行UPDATE

Question

使用RODBC，有像sqlUpdate(channel, dat, ...)这样的函数，允许您传递dat = data.frame(...)而不必构造自己的SQL字符串。

然而，在R的DBI中，我所看到的只有像dbSendQuery(conn, statement, ...)这样的函数，它只接受一个字符串statement，并没有机会直接指定一个data.frame。

那么如何使用DBI更新一个data.frame呢？

- mchen

---

谢谢，这对于INSERT有效，但如何执行UPDATE？ - mchen

Answer 1

我的回答有点晚了，但也许仍然有帮助...

在DBI/odbc包中没有单一的函数（据我所知），但你可以使用准备好的更新语句来复制更新行为（这应该比RODBC的sqlUpdate更快，因为它将参数值作为一批发送到SQL服务器：

library(DBI)
library(odbc)

con <- dbConnect(odbc::odbc(), driver="{SQL Server Native Client 11.0}", server="dbserver.domain.com\\default,1234", Trusted_Connection = "yes", database = "test")  # assumes Microsoft SQL Server

dbWriteTable(con, "iris", iris, row.names = TRUE)      # create and populate a table (adding the row names as a separate columns used as row ID)

update <- dbSendQuery(con, 'update iris set "Sepal.Length"=?, "Sepal.Width"=?, "Petal.Length"=?, "Petal.Width"=?, "Species"=? WHERE row_names=?')

# create a modified version of `iris`
iris2 <- iris
iris2$Sepal.Length <- 5
iris2$Petal.Width[2] <- 1
iris2$row_names <- rownames(iris)  # use the row names as unique row ID

dbBind(update, iris2)  # send the updated data

dbClearResult(update)  # release the prepared statement

# now read the modified data - you will see the updates did work
data1 <- dbReadTable(con, "iris")

dbDisconnect(con)

这仅适用于您拥有主键的情况，例如在上面的示例中使用了行名称，这些行名称是一个唯一数字，每行递增一次...

有关我在DBI dbConnect语句中使用的odbc包的更多信息，请参见：https://github.com/rstats-db/odbc

- R Yoda

---

谢谢。我们尝试一次更新的行数是否有限制？我创建了一个执行MERGE的长SQL语句，如果语句太长，dbGetQuery将返回0（未影响任何行）。 - RockScience

没有简单的答案，因为DBI包只定义了接口，将实现留给不同的DBI兼容包。最终所有限制取决于DBI兼容驱动程序包（例如odbc）和数据库以及DBI驱动程序包使用的二进制DB驱动程序。您是否正在使用odbc包？那么最好的方法是创建一个可重现的代码示例和数据库，并在https://github.com/r-dbi/odbc/issues上打开一个问题（要求修复或至少记录限制）。 - R Yoda

如果您在此处发布了一个新的问题，并提供了一个重现错误的示例，我将会对其进行调查（请随后添加链接以便我能够找到您的问题）。 - R Yoda

我收到了错误消息：Error: Failed to prepare query: ERROR: syntax error at or near "=?" LINE 1: update iris set "Sepal.Length"=?, "Sepal.Width"=?, "Petal.Le... 这可能是什么原因引起的？ - Ruan

@Ruan 请在 Stack Overflow 上开一个新的问题（包括一个最小可重现的示例）来分离这些问题。当然，你可以添加这个答案的链接。谢谢 :-) - R Yoda

Answer 2

在R Yoda的回答基础上，我编写了下面的辅助函数。这可以使用数据框来指定更新条件。

虽然我构建了它来运行事务更新（即单行），但理论上它可以通过传递条件来更新多个行。然而，这与使用输入数据框更新多个行不同。也许有人可以在此基础上继续开发...

dbUpdateCustom = function(x, key_cols, con, schema_name, table_name) {
  
  if (nrow(x) != 1) stop("Input dataframe must be exactly 1 row")
  if (!all(key_cols %in% colnames(x))) stop("All columns specified in 'key_cols' must be present in 'x'")
  
  # Build the update string --------------------------------------------------

  df_key     <- dplyr::select(x,  one_of(key_cols))
  df_upt     <- dplyr::select(x, -one_of(key_cols))
  
  set_str    <- purrr::map_chr(colnames(df_upt), ~glue::glue_sql('{`.x`} = {x[[.x]]}', .con = con))
  set_str    <- paste(set_str, collapse = ", ")
  
  where_str  <- purrr::map_chr(colnames(df_key), ~glue::glue_sql("{`.x`} = {x[[.x]]}", .con = con))
  where_str  <- paste(where_str, collapse = " AND ")
  
  update_str <- glue::glue('UPDATE {schema_name}.{table_name} SET {set_str} WHERE {where_str}')
  
  # Execute ------------------------------------------------------------------
  
  query_res <- DBI::dbSendQuery(con, update_str)
  DBI::dbClearResult(query_res)

  return (invisible(TRUE))
}

作用

• x：包含1个或多个关键列和1个或多个更新列的1行数据框。
• key_cols：字符向量，包含1个或多个列名，这些列是关键字（即在WHERE子句中使用）。

- mkirzon

---

这对我的情况看起来非常有趣。当我的数据框具有多行需要更新时，我遇到了一些错误。dbUpdateCustom 中的错误：输入数据框必须恰好为 1 行。 - Andres Mora

Answer 3

这是一个小助手函数，我使用 REPLACE INTO 和 DBI 来更新表格，替换旧的重复条目为新值。它很基础，只是为了满足我的需求，但应该很容易修改。你需要传递给函数的只有连接、表名和数据框。请注意，表格必须有一个 PRIMARY KEY 列。我还包括了一个简单的工作示例。

row_to_list <- function(Y)  suppressWarnings(split(Y, f = row(Y)))

sql_val <- function(y){
  if(!is.numeric(y)){
    return(paste0("'",y,"'"))
  }else{
    if(is.na(y)){
      return("NULL")
    }else{
      return(as.character(y))
    }
  }
}

to_sql_row <- function(x) paste0("(",paste(do.call("c", lapply(x, FUN = sql_val)), collapse = ", "),")")

bracket <- function(x) paste0("`",x,"`")

to_sql_string <- function(x) paste0("(",paste(sapply(x, FUN = bracket), collapse = ", "),")")

replace_into_table <- function(con, table_name, new_data){
  #new_data <- data.table(new_data)
  cols <- to_sql_string(names(new_data))
  vals <- paste(lapply(row_to_list(new_data), FUN = to_sql_row), collapse = ", ")
  query <- paste("REPLACE INTO", table_name, cols, "VALUES", vals)
  rs <- dbExecute(con, query)
  return(rs)
}

tb <- data.frame("id" = letters[1:20], "A" = 1:20, "B" = seq(.1,2,.1)) # sample data
dbWriteTable(con, "test_table", tb) # create table
dbExecute(con, "ALTER TABLE test_table ADD PRIMARY KEY (id)") # set primary key

new_data <- data.frame("id" = letters[19:23], "A" = 1:5, "B" = seq(101,105)) # new data
new_data[4,2] <- NA # add some NA values
new_data[5,3] <- NA
table_name <- "test_table"
replace_into_table(con, "test_table", new_data)

result <- dbReadTable(con, "test_table")

- Seth