Search code examples
rr-dbirsqlite

Appending new data to sqlite db in R

I have created a table in a sqlite3 database from R using the following code:-

con <- DBI::dbConnect(drv = RSQLite::SQLite(),
                      dbname="data/compfleet.db")
s<- sprintf("create table %s(%s, primary key(%s))", "PositionList",
                             paste(names(FinalTable), collapse = ", "),
                             names(FinalTable)[2])
dbGetQuery(con, s)

dbDisconnect(con)

The second column of the table is UID which is the primary key. I then run a script to update the data in the table. The updated data could contain the same UID which already exists in the table. I don't want these existing records to be updated and just want the new records(with new UID values) to be appended to this database. The code I am using is:-

DBI::dbWriteTable(con, "PositionList", FinalTable, append=TRUE, row.names=FALSE, overwite=FALSE)

Which returns an error:

Error in result_bind(res@ptr, params) : 
  UNIQUE constraint failed: PositionList.UID

How can I achieve the task of appending only the new UID values without changing the existing UID values even if they appear when I run my updation script?

Solution

  • You can query the existing UIDs (as a one-column data frame) and remove corresponding rows from the table you want to insert.

    uid_df <- dbGetQuery(con, "SELECT UID FROM PositionList")
dbWriteTable(con, "PositionList", FinalTable[!(FinalTable$UID %in% uid_df[[1]]), ], ...)