How to insert values to the SQLite database from R.Data?

Question

I am trying to insert data to sqlite database from R data.frame but I failed.

Here is the code in R studio I used.

So there two loops using which I'm trying to load all dbf files listed in the specified folders (working directory). And then I'm trying to insert data from R data.frame (df[[1]]) to the sqlite database (I have already created it) by sqldf or by dbExecute functions.

In case of dbExecute the function cannot read table from R data.frame (in this case df[[1]]).

In case of sqldf the function doesn't see all_banks table created earlier in the database.

Any ideas of how to handle this problem? Thank to all.

library(sqldf)
library(DBI)
library(foreign)
library("RSQLite")



setwd("F:~/Data")
con <- dbConnect(RSQLite::SQLite(), dbname = "banks.db")
for(path in c("F:~/123-20190901",
          "F:~/123-20190801")){
  setwd(path)
  ldf <- list()
  listdbf <- dir(pattern = "*.DBF")
  for (k in 1:length(listdbf)){
     ldf[[k]] <- read.dbf(listdbf[k])
     }

  df1 <- ldf[[1]]
  df2 <- ldf[[2]]
  dbExecute(con, "insert into all_banks select DT, REGN, name_b from df1")
  sqldf("insert into all_banks select DT, REGN, name_b from df1")
}
dbDisconnect(con)

Error: no such table: df1
Error: no such table: all_banks

Parfait · Accepted Answer · 2019-10-29 18:40:02Z

Fundamentally, sqldf and dbExecute are two different processes where the former works in local environment and latter works in an external database (albeit with noted exceptions).

Per docs,

sqldf is an R package for running SQL statements on R data frames, optimized for convenience.

Therefore, sqldf, runs on R data frames in local environment. By default, sqldf is not used to make changes to a persistent, external database. Technically, it runs an in-memory SQLite database which does not save after R session. Consequently, if all_banks is not a data frame in global environment, it will not be recognized by sqldf. With that said, there is an advanced way of using sqldf with a permanent SQLite database. But below solution is arguably easier.

Per docs, DBI::dbExecute

Executes a statement and returns the number of rows affected.

With first argument:

conn: A DBIConnection object, as returned by dbConnect().

Therefore, dbExecute runs commands on an external database and within its scope does not use local environment objects. Consequently, if df1 is not a table in database, it will not be recognized by dbExecute.

Solution

To resolve your database append needs, simply use DBI's dbWriteTable which pushes a local R data frame into an external database table (assuming same structure on both end points unless using overwrite=TRUE). Such changes to table will remain permanent even after closing database connection or R session.

dbWriteTable(con, name="all_banks", value=df1, append=TRUE)

Collectives™ on Stack Overflow

How to insert values to the SQLite database from R.Data?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related