r - dbplyr:从数据库中的表中删除行

Question

什么是 dbplyr相当于DBI::dbSendQuery(con, "DELETE FROM <table> WHERE <condition>")的动词组合.

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

我想在 dplyr 中完成方式，但我不确定这是否可能。我在包引用中找不到任何类似的东西。

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

所以除非你需要修改数据库，否则我建议过滤而不是删除。