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!

Package `sqldf` is worth a check. It allows easy interaction with dataframes and sql tables. — Dominic Comtois, Oct 26 '14 at 02:37
See https://stackoverflow.com/questions/26568182/is-it-possible-to-insert-add-a-row-to-a-sqlite-db-table-using-dplyr-package/26784801#comment80776297_26784801 and https://github.com/tidyverse/dplyr/issues/3120#issuecomment-339034612 -- it is somewhat possible, but awkward, and recommended against. Use DBI:: instead. — Dave X, Oct 24 '17 at 16:52

score 57 · Answer 1 · edited Sep 22 '17 at 06:21

57

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

edited Sep 22 '17 at 06:21

Dave X

4,831
4
31
42

answered Nov 06 '14 at 16:44

StatSandwich

1,134
11
14

I think it's db_insert_into( con = my_db$con, table = "my_table", **values** = newdf) – dalloliogm May 11 '15 at 21:26
reinserting iris worked for me: `db_insert_into( con = my_db$con, table = "my_table", values = iris)` – Dave X Sep 22 '17 at 03:45
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. – Dave X Sep 22 '17 at 14:37
10

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. – hadley Oct 24 '17 at 15:36

Paul Rougieux · Answer 2 · 2016-03-08T08:24:16.063

17

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)

edited Mar 08 '16 at 08:24

answered Dec 12 '14 at 00:16

Paul Rougieux

10,289
4
68
110

1

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. – Paul Rougieux Dec 12 '14 at 00:20
Thanks anyhow! It's interesting additional information. – rdatasculptor Dec 12 '14 at 23:09

score 10 · Accepted Answer · answered Oct 26 '14 at 02:34

10

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")')

answered Oct 26 '14 at 02:34

hrbrmstr

77,368
11
139
205

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? – rdatasculptor Oct 26 '14 at 07:27
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". – rdatasculptor Oct 26 '14 at 07:47
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! – rdatasculptor Oct 26 '14 at 10:14
13

Instead of constructing the query by hand, it would be better to use `dbWriteTable(append = TRUE)` – hadley Oct 24 '17 at 15:37

score 1 · Answer 4 · answered Oct 06 '15 at 19:19

1

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

answered Oct 06 '15 at 19:19

Kevin Ogoro

397
3
6

You need not switch to SQL, but you can use `DBI::dbWriteTable()` etc. in R. – Dave X Oct 24 '17 at 16:54

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

4 Answers4

Linked