rpostgreSQL:根据时间覆盖数据

Question

我们的财务团队每月都会使用shiny app将csv文件上传到postegreSQL。有时，他们需要修改数据，然后再次上传。

让我们举一些例子，让这个问题更容易理解：

# Retrieve data from PostgreSQL
>monthly_expense <- dbGetQuery(con, "SELECT * from expense_table2")
>monthly_expense
>month   type  USD
 201605    A   200
 201605    B   300
 201606    A   105
 201606    B   200

 # Produce new 201606 data
 >month<-c("201606", "201606")
 >type<-c("A", "B")
 >USD<-c(150, 250)
 >new_data<-data.frame(month, type, USD )
 >new_data
    month    type USD
 1  201606    A   150
 2  201606    B   250

那么我如何用新的数据替换201606个数据呢？我认为应该修改以下命令，以指定覆盖2016年的数据：

 dbWriteTable(con, "expense_table2", value = new_data, append=T, overwrite = TRUE)  

Answer 1

考虑使用dbSendQuery的更新查询

sqlstrings <- paste0("UPDATE expense_table2 SET USD = ", newdata$USD , " 
                      WHERE month='", newdata$month, "' AND type='", newdata$type, "';")

queryruns <- lapply(sqlstrings, function(x) dbSendQuery(con, x))

或者，更新现有的数据帧，然后用dbWriteTable覆盖Postgres的db表

index <- match(monthly_expense$month, new_data2$month)
monthly_expense[index, 2:3] <- new_data2[2:3]

dbWriteTable(con, "expense_table2", value = monthly_expense, append=T, overwrite = TRUE) 

更重要的是，将未更新的行rbind到newdata并将其推送到数据库：

newdata <- rbind(monthly_expense[!(monthly_expense$month %in% newdata$month),],
                 newdata)

dbWriteTable(con, "expense_table2", value = new_data, append=T, overwrite = TRUE)