将列添加到sqlite数据库

Question

我想将我在R中生成的向量添加到sqlite表中作为新列。为此，我想使用dplyr（我根据这个帖子here安装了最新的开发版本以及dbplyr包）。我试过的：

library(dplyr) 
library(DBI) 

#creating initial database and table 
dbcon  <- dbConnect(RSQLite::SQLite(), "cars.db") 
dbWriteTable(dbcon, name = "cars", value = cars) 
cars_tbl <- dplyr::tbl(dbcon, "cars") 

#new values which I want to add as a new column 
new_values <- sample(c("A","B","C"), nrow(cars), replace = TRUE) 

#attempt to add new values as column to the table in the database 
cars_tbl %>% mutate(new_col = new_values) #not working 

什么是简单的方法来实现这一点（不一定与dplyr）？

Answer 1

不知道用dyplr这样做的方法，但可以直接使用RSQLite。问题实际上并不是RSQLite，但是我不知道如何将列表传递给mutate。需要注意的是，在你的代码，像这样的工作：

cars_tbl %>% mutate(new_col = another_column/3.14) 

无论如何，我的选择。我创建了一个玩具cars数据框。

cars <- data.frame(year=c(1999, 2007, 2009, 2017), model=c("Ford", "Toyota", "Toyota", "BMW")) 

我打开连接，实际上创建表，

dbcon <- dbConnect(RSQLite::SQLite(), "cars.db") 
dbWriteTable(dbcon, name = "cars", value = cars) 

添加新列和检查，

dbGetQuery(dbcon, "ALTER TABLE cars ADD COLUMN new_col TEXT") 
dbGetQuery(dbcon, "SELECT * FROM cars") 
    year model new_col 
1 1999 Ford <NA> 
2 2007 Toyota <NA> 
3 2009 Toyota <NA> 
4 2017 BMW <NA> 

然后你就可以更新新列，但唯一棘手事情是，你必须提供一个where声明，在这种情况下，我使用年份。

new_values <- sample(c("A","B","C"), nrow(cars), replace = TRUE) 
new_values 
[1] "C" "B" "B" "B" 

dbGetPreparedQuery(dbcon, "UPDATE cars SET new_col = ? where year=?", 
        bind.data=data.frame(new_col=new_values, 
             year=cars$year)) 

dbGetQuery(dbcon, "SELECT * FROM cars") 
    year model new_col 
1 1999 Ford  C 
2 2007 Toyota  B 
3 2009 Toyota  B 
4 2017 BMW  B 

作为唯一索引，你总是可以使用rownames(cars)，但你必须将其添加为您的数据帧，然后在表中的列。

建议后编辑由@krlmlr：确实要好得多使用dbExecute代替过时dbGetPreparedQuery，评论后

dbExecute(dbcon, "UPDATE cars SET new_col = :new_col where year = :year", 
      params=data.frame(new_col=new_values, 
          year=cars$year)) 

编辑：我没想到这个前几天，但即使它是一个SQLite你可以使用rowid。我已经测试过这个，它工作。

dbExecute(dbcon, "UPDATE cars SET new_col = :new_col where rowid = :id", 
      params=data.frame(new_col=new_values, 
          id=rownames(cars))) 

尽管您必须确保表中的rowid与您的rownames相同。无论如何，你总是可以得到你的rowid是这样的：

dbGetQuery(dbcon, "SELECT rowid, * FROM cars") 
    rowid year model new_col 
1  1 1999 Ford  C 
2  2 2007 Toyota  B 
3  3 2009 Toyota  B 
4  4 2017 BMW  B