Sqldf: create table from data frame error: "no such table." and two tables created instead of one

Question

I recently updated R, RSQLite and sqldf (versions below).

Usually:

sqldf('create table foo as select * from bar', db = 'test.db') 

should create a table named 'foo' in the attached sqlite database using the data frame 'bar' if it exists to load a new table.

Instead, I get a "no such table" error, and when I look at the database, the tables "foo" and "bar" are created.

Playable example:

 library(RSQLite) library(sqldf) mydb = 'test.db' ## remove file if it exists system(paste('rm', mydb)) ## open connection ##con <- dbConnect(SQLite(), dbname=mydb) system(paste('ls -l', mydb)) sqldf( paste0( 'attach "', mydb, '" as new' ) ) system(paste('ls -l', mydb)) class(mtcars) sqldf( 'create table mycars as select * from mtcars', dbname = mydb ) sqldf('select * from sqlite_master', dbname = mydb) sqldf('select * from main.mycars limit 1', dbname = mydb) sqldf('select * from main.mtcars limit 1', dbname = mydb) sessionInfo() 

which creates two tables and throws an error (to add insult to injury):

  > library(RSQLite) > library(sqldf) Loading required package: gsubfn Loading required package: proto > mydb = 'test.db' > ## remove file if it exists > system(paste('rm', mydb)) > ## open connection > ##con <- dbConnect(SQLite(), dbname=mydb) > system(paste('ls -l', mydb)) ls: test.db: No such file or directory > sqldf( paste0( 'attach "', mydb, '" as new' ) ) Loading required package: tcltk data frame with 0 columns and 0 rows > system(paste('ls -l', mydb)) -rwxrwxrwx 1 nathan staff 1 Jan 6 10:01 test.db > class(mtcars) [1] "data.frame" > sqldf( 'create table mycars as select * from mtcars', dbname = mydb ) Error in rsqlite_send_query( conn@ptr , statement) : no such table: `mtcars` In addition: Warning message: Quoted identifiers should have class SQL, use DBI::SQL() if the caller performs the quoting. > sqldf('select * from sqlite_master', dbname = mydb) type name tbl_name rootpage 1 table mtcars mtcars 2 2 table mycars mycars 5 sql 1 CREATE TABLE `mtcars` (\n "mpg" REAL,\n "cyl" REAL,\n "disp" REAL,\n "hp" REAL,\n "drat" REAL,\n "wt" REAL,\n "qsec" REAL,\n "vs" REAL,\n "am" REAL,\n "gear" REAL,\n "carb" REAL\n) 2 CREATE TABLE mycars(\n mpg REAL,\n cyl REAL,\n disp REAL,\n hp REAL,\n drat REAL,\n wt REAL,\n qsec REAL,\n vs REAL,\n am REAL,\n gear REAL,\n carb REAL\n) > sqldf('select * from main.mycars limit 1', dbname = mydb) mpg cyl disp hp drat wt qsec vs am gear carb 1 21 6 160 110 3.9 2.62 16.46 0 1 4 4 > sqldf('select * from main.mtcars limit 1', dbname = mydb) mpg cyl disp hp drat wt qsec vs am gear carb 1 21 6 160 110 3.9 2.62 16.46 0 1 4 4 > sessionInfo() R version 3.3.2 (2016-10-31) Platform: x86_64-apple-darwin13.4.0 (64-bit) Running under: OS X El Capitan 10.11.6 locale: [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8 attached base packages: [1] tcltk stats graphics grDevices utils datasets methods base other attached packages: [1] sqldf_0.4-10 gsubfn_0.6-6 proto_1.0.0 RSQLite_1.1-1 devtools_1.12.0 loaded via a namespace (and not attached): [1] DBI_0.5-1 withr_1.0.2 Rcpp_0.12.8 memoise_1.0.0 digest_0.6.11 chron_2.3-48 Is this a bug or some new intended behavior? Thanks for your help. 

Answer 1

UPDATE: the latest versions of RSQLite and sqldf do not have the incompatibility problems resolved by this issue i.e.: sqldf_0.4-10 RSQLite_1.1-2 work well together - Nathan

All: Thanks to G. Grothendieck for pointers to the incompatibility problems presented by RSQLite 1.1-1. As indicated in the comment on the answer, change RSQLite to 1.0.0:

 devtools::install_url("https://cran.r-project.org/src/contrib/Archive/RSQLite/RSQLite_1.0.0.tar.gz") 

Answer 2

This is really a compatibility issue between the current version of RSQLite and sqldf . RSQLite now more stringent with respect to the arguments it takes for dbReadTable() , dbWriteTable() and dbRemoveTable() , warnings will be issued (but only once per session) until sqldf is adapted.

In the near future I will release a compatibility update, there is a problem on GitHub that will contain run updates.