Can't Insert dataframe to SQLite table when column names different dbWriteTable

Question

I am using an SQLite database in R. To insert a data frame into an SQLite table using dbWriteTable from DBI, it seems that I need to have the same column names in the data frame as those in the table. I was using sqldf and this was not a condition, it just insert based on the order of columns. Is there a way to change the behaviour of dbWriteTable to accept my data frame. Here is an example code with both dbWriteTable and sqldf

library(RSQLite)
library(sqldf)

path = "data_base.sqlite"

conn = DBI::dbConnect(RSQLite::SQLite(),path)
dbExecute(conn, "CREATE TABLE sales(Items INT, Sales REAL)")

df1 = data.frame(Items = c(12,14,5), Sales = c(111.6,130.2,46.5))
dbWriteTable(conn,name = "sales",value = df1, append=TRUE, row.names=FALSE)

df2 = data.frame(Nombre = c(2,6,9), Ventes = c(18.6,55.8,83.7))
dbWriteTable(conn,name = "sales",value = df2, append=TRUE, row.names=FALSE)

sqldf("insert into sales select * from `df2`",dbname = path)

score 1 · Accepted Answer · answered Nov 25 '20 at 14:51

Up front, this is a really bad idea: if the columns are in the wrong order or if there is an incorrect number of columns, then this will produce unpredictable (or just bad) results.

Having said that, rename the frame's column names before uploading.

df2 = data.frame(Nombre = c(2,6,9), Ventes = c(18.6,55.8,83.7))
names(df2)
# [1] "Nombre" "Ventes"
names(df2) <- dbListFields(conn, "sales")
names(df2)
# [1] "Items" "Sales"
dbWriteTable(conn,name = "sales",value = df2, append = TRUE, row.names = FALSE)
DBI::dbGetQuery(conn, "select * from sales")
#   Items Sales
# 1    12 111.6
# 2    14 130.2
# 3     5  46.5
# 4     2  18.6
# 5     6  55.8
# 6     9  83.7

If you don't want to change the names (for some reason) of df2, then you can do it "inline":

dbWriteTable(
  conn, name = "sales",
  value = setNames(df2, dbListFields(conn, "sales")),
  append = TRUE, row.names = FALSE)

Other than that, then ... No, you should not change dbWriteTable to ignore column names and just assume things are aligned.

Clever solution. I am not sure if and when R does create a copy of the data.frame though (could be added to the answer to make it a "premium answer" ;-) — R Yoda, Aug 24 '22 at 22:19

score 0 · Answer 2 · answered Nov 22 '20 at 10:54

0

The column names must correspond. Ignore the warning or use sqldf2 defined here to muffle it.

sqldf("insert into sales select Nombre as Items, Ventes as Sales from df2",
   dbname = path)

answered Nov 22 '20 at 10:54

G. Grothendieck

254,981
17
203
341

Thanks for your answer. I was asking if we can do this with dbWriteTable. – Abdelouahed BEN MHAMED Nov 22 '20 at 16:39

Can't Insert dataframe to SQLite table when column names different dbWriteTable

2 Answers2