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!

Archie

Package sqldf is worth a check. It allows easy interaction with dataframes and sql tables. – Drakensberg

See #26568682 and github.com/tidyverse/dplyr/issues/3120#issuecomment-339034612 -- it is somewhat possible, but awkward, and recommended against. Use DBI:: instead. – Geophilous

Answer 1

You can perform SQL operations on a database/table created via dplyr, but you have to revert to RSQLite/DBI calls and change how you made the database/table:

library(dplyr)

my_db <- src_sqlite("my_db.sqlite3", create=TRUE) 
copy_to(my_db, iris, "my_table", temporary=FALSE) # need to set temporary to FALSE

# grab the db connection from the object created by src_sqlite
# and issue the INSERT That way

res <- dbSendQuery(my_db$con, 
                   'INSERT INTO my_table VALUES (9.9, 9.9, 9.9, 9.9, "new")')

Lisp

Thanks! It works. Do you think it is possible to add entire tables to an existing one as well, instead of just one row? Or is that purely an SQL question? – Archie

okay, I guess I can use the dbWriteTable function instead of dbSendQuery. Though I keep on getting this message: "In sqliteWriteTable(conn, name, value, ...) : unable to begin transaction". – Archie

I was too quick with my comments. Very recently there was an RSQLite update that I hadn't installed yet. Everything works as planned now. Thanks again hrbrmstr! – Archie

Instead of constructing the query by hand, it would be better to use dbWriteTable(append = TRUE) – Eastlake

Answer 2

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

Militarist

I think it's db_insert_into( con = my_db$con, table = "my_table", values = newdf) – Eunuchoidism

reinserting iris worked for me: db_insert_into( con = my_db$con, table = "my_table", values = iris) – Geophilous

Per the help docs, db_write_table(...,append=TRUE) is intended to replace the deprecated db_insert_into() function, but as of 2017-09-22, it does not appear to work with sqlite3. – Geophilous

You can, but you shouldn't. dplyr is designed specifically for retrieving data from databases, not modifying in place. This answer uses internal functions - I strongly recommend against using these functions. – Eastlake

Answer 3

In this newsgroup. Hadley explained the purpose of the function dplyr::copy_to(). It is intended to create temporary test tables. The email exchange ends by suggesting to use RMySQL::dbWriteTable() to append data to an existing table. The same applies to SQLite databases, as explained in the accepted answer above.

To append a data frame dtf which has the same column names as an existing database table, I used:

library(RMySQL)
DB <- dbConnect(MySQL(), user="usename", host="localhost",
                   password="***", dbname="dbname")
dbWriteTable(DB, "tablename", dtf, append=TRUE, row.names = FALSE)

Nebula

sorry I realise your question was about adding rows to an SQLite table. I landed here during my search. Maybe I leave my answer for the moment. – Nebula

Thanks anyhow! It's interesting additional information. – Archie

Answer 4

The main reason I use dplyr to write into a database is that I do not want to switch between languages mid code. For your question the best solution I think is to use the db_insert_into() function of dplyr as illustrated by StatSandwich

Doorn

You need not switch to SQL, but you can use DBI::dbWriteTable() etc. in R. – Geophilous