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)
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.