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

问题描述

使用 RODBC 时，就有类似sqlUpdate(channel, dat, ...)的功能允许您传递dat = data.frame(...)而不用构造自己的SQL字符串.

With RODBC, there were functions like sqlUpdate(channel, dat, ...) that allowed you pass dat = data.frame(...) instead of having to construct your own SQL string.

但是，有了R的 DBI ，我所看到的只是像dbSendQuery(conn, statement, ...)这样的函数，它仅接受字符串statement，并且没有机会直接指定data.frame.

However, with R's DBI, all I see are functions like dbSendQuery(conn, statement, ...) which only take a string statement and gives no opportunity to specify a data.frame directly.

那么如何在DBI中将data.frame与UPDATE一起使用?

So how to UPDATE using a data.frame with DBI?

推荐答案

我的回答真的很晚，但也许仍然有帮助...

Really late, my answer, but maybe still helpful...

DBI/odbc软件包中没有单个函数(我知道)，但是您可以使用准备好的更新语句来复制更新行为(它应该比RODBC的sqlUpdate更快，因为它将批处理参数值发送给SQL服务器:

There is no single function (I know) in the DBI/odbc package but you can replicate the update behavior using a prepared update statement (which should work faster than RODBC's sqlUpdate since it sends the parameter values as a batch to the SQL server:

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)

仅当您具有我在上例中通过使用行名创建的主键时，行名才是唯一的数字，每行将其增加一个...

This works only if you have a primary key which I created in the above example by using the row names which are a unique number increased by one for each row...

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

For more information about the odbc package I have used in the DBI dbConnect statement see: https://github.com/rstats-db/odbc

这篇关于如何使用R DBI传递data.frame以进行UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！