r - Is it possible to insert (add) a row to a SQLite db table using dplyr package?

Question

I am new to the database connection capabilities of dplyr package, but I am very interested in using it for an SQLite connection. I followed this tutorial and created an SQLite database (my_db)

my_db <- src_sqlite("my_db.sqlite3", create = T)

and inserted a dataframe (df) as a table (my_table) of this database.

copy_to(my_db,df,"my_table")

Now I want to insert new rows in this table. I tried something like this (and yes I must admit it doesn't even look like promising... but I still gave it a try):

collect(build_sql("INSERT INTO my_table VALUES (",newdf,")", con=my_db))

Does anyone know if adding rows to an existing sqlite db table is even possible using dplyr? Or how would you deal with this problem? Many thanks in advance!

Answer 1

No, you can do this all within dplyr.

require(dplyr)

my_db <- src_sqlite( "my_db.sqlite3", create = TRUE)                 # create src
copy_to( my_db, iris, "my_table", temporary = FALSE)                 # create table
newdf = iris                                                         # create new data
db_insert_into( con = my_db$con, table = "my_table", values = newdf) # insert into