CODEWITHSUNDEEP
rdbirsqlite
Abdelouahed BEN MHAMED
Abdelouahed BEN MHAMED

Reputation: 157

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

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)

Upvotes: 1

Views: 1081

Answers (2)

r2evans
r2evans

Reputation: 160437

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.

Upvotes: 1

G. Grothendieck
G. Grothendieck

Reputation: 269526

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)

Upvotes: 0

Related Questions