Is there an "executemany" analog for R and RSQLite?

Question

Question:
Is there a way to insert many rows (under 20k) into an SQLite3 table using r?

More work:
I have looked at similar answers, but they do not seem to speak to this:

• Is it possible to insert multiple rows at a time in an SQLite database?
• How to insert values into sqlite3 using autohotkey
• Is there a simple way to use RSQLite and `sqldf` with more than 999 variables?
• dbWriteTable operation in RSQLite become slower
• Insert multiple rows in SQLite

There is a "c command" but that is not "R".

The SQLite3 help itself doesn't seem to give a way forward:

• https://www.sqlite.org/lang_insert.html

Python has "execute many" which makes large inserts operate quickly. Is there an "R" analog in RSQLite or DBI? Is there a different library that I should use?

Thanks in advance.

Answer 1

dbWriteTable has an append argument to allow adding on to a table. Using the built in BOD data frame:

library(RSQLite)
con <- dbConnect(SQLite())
dbWriteTable(con, "BOD", BOD) # create the table

dbWriteTable(con, "BOD", 2*BOD, append = TRUE) # add some more rows

Answer 2

there is a proper way to iterate over rows in a data.frame or tibble and insert each row one by one in a SQLite table. the easy solution with dbWriteTable doesn't provide much granularity in the table parametrization and INSERT conditions, which are oftentimes needed (e.g. to set primary and foreign keys).

example with a time series stored in a tibble with 2 columns 'times' and 'values'

1. Create table

con <- DBI::dbConnect(RSQLite::SQLite(), db_path)

table_name <- 'my_ts'

com <- as.character(stringr::str_glue("CREATE TABLE IF NOT EXISTS '{table_name}' \
                                     ('times' TEXT NOT NULL, 'values' REAL, PRIMARY KEY(times));"))

DBI::dbWithTransaction(con, {
     DBI::dbExecute(con, com)
    })

2. Store tibble columns as vectors

tb_times <- as.character(tb[['times']])
tb_values <- as.numeric(tb[['values']])

3. Insert rows

for (i in seq_along(index(tb))) {
    com <- as.character(stringr::str_glue("INSERT OR REPLACE INTO '{table_name}'(times, 'values') \
                                        VALUES('{tb_times[i]}', {tb_values[i]});"))
    DBI::dbWithTransaction(con, {
        DBI::dbExecute(con, com)
    })
}

RSQLite::dbDisconnect(con)

p.s. 'values' is inbetween quotes in the INSERT statement as it is a reserved word in SQLite