dbplyr：从数据库表中删除行

Question

如何使用dbplyr动词组合来达到DBI::dbSendQuery(con, "DELETE FROM <table> WHERE <condition>")的效果。

我想做的不是从数据库中查询数据，而是从表中删除或更新数据。

我希望能够用dplyr的方式实现，但我不确定是否可能。我在包的参考文档中没有找到类似的内容。

- englealuze

Answer 1

dbplyr可以将dplyr命令翻译为查询数据库表的命令。我不知道是否有内置的方法可以使用纯dbplyr修改现有的数据库表。

这可能是一种设计选择。

• 在R中，我们不需要区分从表中获取数据（查询）和修改表。这可能是因为在R中，如果出现错误/错误，我们可以重新加载原始数据到内存中。
• 但在数据库中，查询和修改表是故意不同的事情。当修改数据库时，您正在修改源，因此使用了其他控件（因为恢复已删除的数据要困难得多）。

DBI包可能是修改数据库的最佳选择

这是我所有dbplyr工作的方法。通常使用自定义函数，该函数接受由dbplyr翻译产生的查询并将其插入到DBI调用中（您可以在我的dbplyr helpers GitHub存储库中看到此类示例）。

考虑这两种方法：（1）反连接（在所有列上）然后编写新表，（2）使用DELETE FROM语法。

反连接方法的模拟

records_to_remove = remote_table %>%
  filter(conditions)

desired_final_table = remote_table %>%
  anti_join(records_to_remove, by = colnames(remote_table))

query = paste0("SELECT * INTO output_table FROM (",
               sql_render(desired_final_table),
               ") AS subquery")

DBI::dbExecute(db_con, as.character(query))

DELETE FROM语法的模拟

records_to_remove = remote_table %>%
  filter(conditions)

query = sql_render(records_to_remove) %>%
  as.character() %>%
  gsub(search_term = "SELECT *", replacement_term = "DELETE")

DBI::dbExecute(db_con, query)

如果您计划多次运行这些查询，则建议将其包装在一个函数中，并进行有效性检查。

对于某些用例，删除行可能是不必要的。

您可以将 R 中的 filter 命令视为从表中删除行。例如，在 R 中，我们可能会运行：

prepared_table = input_table %>%
  filter(colX == 1) %>%
  select(colA, colB, colZ)

可以将其视为在生成输出之前删除 colX == 1 的行：

output = prepared_table %>%
  group_by(colA) %>%
  summarise(sumZ = sum(colZ))

（或者您可以使用反连接而不是过滤器。）

但是对于这种类型的删除，您不需要编辑源数据，因为您可以每次在运行时过滤掉不需要的行。是的，这将使您的数据库查询变大，但这在使用数据库时是正常的。

因此，在SQL中组合准备和输出是正常的（类似于这样）：

SELECT colA, SUM(colZ) AS sumZ
FROM (

  SELECT colA, colB, colZ
  FROM input_table
  WHERE colX = 1

) AS prepared_table
GROUP BY colA

因此，除非您需要修改数据库，否则我建议使用过滤而不是删除。

- Simon.S.A.

---

感谢您提供全面的答案。关于您回答的第二部分，我了解在SQL中，查询通常与多个逻辑结合使用，包括使用where语句进行分组和过滤，这与dplyr不同，dplyr中的处理逻辑可以通过管道清晰地分离。但我不明白这与从表中删除行有什么关系。您的实际观点是什么？您能用另一种方式解释一下吗？谢谢。 - englealuze

好的，我的第二个例子并没有清楚地回答你的问题。我更是想让未来的读者认为从远程表中删除行比仅仅过滤它们更好。我会编辑我的答案，使其更加清晰明了。 - Simon.S.A.

希望这些编辑能使第二部分更清晰。从你的问题来看，似乎你打算修改数据库。因此，第二部分对你来说不那么相关，更多地是为了未来回答的尝试。 - Simon.S.A.

Answer 2

这里是对@Simon.S.A的删除代码的另一种替代方案：

dbDeleteCustom = function(x, con, schema_name, table_name) {
      #x: 1-row dataframe that contains 1 row for deletion
      if (nrow(x) != 1) stop("Input dataframe must be exactly 1 row")

      where_str  <- purrr::map_chr(colnames(x), ~glue::glue_sql("{`.x`} = {x[[.x]]}", .con = con))
      where_str  <- paste(where_str, collapse = " AND ")

      update_str <- glue::glue("DELETE FROM {schema_name}.{table_name} WHERE {where_str}")

      # Execute ------------------------------------------------------------------

      query_res <- DBI::dbSendQuery(con, update_str)
      rowsAffected <- dbGetRowsAffected(query_res)
      DBI::dbClearResult(query_res)

      return (rowsAffected)
    }

这段代码是基于这篇帖子中的代码：如何使用R DBI传递data.frame进行UPDATE操作 正如@Simon建议的那样，请考虑使用包装函数。这些函数可以包括：

tryCatch(
        pool::poolWithTransaction(con, function(conn) {
          rowsAffected <- dbDeleteCustom(record_to_remove, conn, schema, "tableName ")

          log_step(paste0("removed entries for action, rows affected: ", rowsAffected))
        }
        ),
        error = function(e){
          "handle error - code"
        }
      )

- Aaron C