dbWriteTable to SQLite posting duplicates of my data frame

I've been doing processing data that results in a data frame of 4860 observations. I write that to a Results table in a SQLite database like this:

db = dbConnect(RSQLite::SQLite(), dbname=DATAFILE)
dbWriteTable(db, "Results", my_dataframe, append = TRUE)
dbDisconnect(db)

Then I process some more data and later write it to the same table using this same code.

The problem is, every now and again, what's written to my SQLite file is some multiple of the 4860 records I expect. Just now it was 19448 (exactly 4X the 4860 records that I can see in RStudio are in my data frame).

New information: While debugging this I've been checking the number of records written to the SQLite database table after every dbWriteTable() call. When it's not correct, I've used a SQL command to delete the records of that batch. When I run the above code again, I get another multiple of records written (e.g., instead of 4X, it will be 5X; if I do it again, it will be 6X).

This seems such a random problem. As I know the data frame contents is correct, I feel as though the problem must be in my use of dbWriteTable(). Any guidance would be appreciated. Thank you.

-- Robert

Turns out this problem is not with RSQLite but with the tool I was using (SQLPro for SQLite) to review progress (and make deletions when things went awry). When handled strictly in R code using RSQLite, all is well.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.