将列添加到sqlite数据库 [英] Adding column to sqlite database
问题描述
我试图将在R中生成的向量添加到sqlite表中作为新列。为此,我想使用 dplyr
(我根据此安装了最新的开发版本以及 dbplyr
软件包。在此处)。我尝试过的操作:
I am trying to add a vector which I generated in R to a sqlite table as a new column. For this I wanted to use dplyr
(I installed the most recent dev. version along with the dbplyr
package according to this post here). What I tried:
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 )?
What is an easy way to achieve this (not necessarily with dplyr)?
推荐答案
不知道使用 dyplr
的方法,但是您可以直接使用 RSQLite
完成。问题实际上不是 RSQLite
,而是我不知道如何将列表传递给 mutate
的事实。 。请注意,在您的代码中,类似的方法将起作用:
Not aware of a way of doing this with dyplr
, but you can do it with RSQLite
directly. The problem is not actually with RSQLite
, but the fact that I don't know how to pass a list to mutate
. Note that, in your code, something like this would work:
cars_tbl %>% mutate(new_col = another_column / 3.14)
无论如何,我的选择。我创建了一个玩具汽车
数据框。
Anyway, my alternative. I've created a toy cars
dataframe.
cars <- data.frame(year=c(1999, 2007, 2009, 2017), model=c("Ford", "Toyota", "Toyota", "BMW"))
我打开连接并实际创建表,
I open connection and actually create the table,
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
语句,在这种情况下,我使用年份。
And then you can update the new column, but the only tricky thing is that you have to provide a where
statement, in this case I use the year.
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
作为唯一索引,您可以随时使用行名(汽车)
,但您必须将其添加为数据框中的列,然后再添加至表中。
As a unique index, you could always use rownames(cars)
, but you would have to add it as a column in your dataframe and then in your table.
@krlmlr建议后进行编辑:使用起来确实好得多 dbExecute
而不是已弃用的 dbGetPreparedQuery
,
EDIT after suggestion by @krlmlr: indeed much better using dbExecute
instead of deprecated 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
。我已经对此进行了测试。
EDIT after comments: I did not think about this a few days ago, but even if it is a SQLite
you can use the rowid
. I've tested this and it works.
dbExecute(dbcon, "UPDATE cars SET new_col = :new_col where rowid = :id",
params=data.frame(new_col=new_values,
id=rownames(cars)))
尽管您必须确保表中的rowid与您的行名相同。无论如何,您总是可以这样获得rowid:
Although you have to make sure that the rowid's in the table are the same as your rownames. Anyway you can always get your rowid's like this:
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
这篇关于将列添加到sqlite数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!