Adding a column to sqlite database

Question

I am trying to add a vector that I generated in R to the sqlite table as a new column. For this I wanted to use dplyr(I installed the latest version of dev along with the package dbplyraccording 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

What is an easy way to achieve this (not necessarily with dplyr)?

Answer 1

dyplr, RSQLite . RSQLite, , , mutate. , - :

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

, . cars dataframe.

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), , .

EDIT @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))

EDIT . , 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 . 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