r - sqldf : create table from data frame error: "no such table". 和创建了两个表而不是一个

Question

我最近升级了 R、RSQLite 和 sqldf(以下版本)。

一般:

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

应该在附加的 sqlite 数据库中创建一个名为 'foo' 的表，使用数据框 'bar'(如果存在)来加载新表。

相反，我收到了“没有这样的表”错误，而且当我查看数据库时，同时创建了“foo”和“bar”表。

可重现的例子:

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

它产生两个表并抛出一个错误(雪上加霜):

    > 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

更新:最新版本的 RSQLite 和 sqldf 没有此问题解决的不兼容问题
IE。:
sqldf_0.4-10 RSQLite_1.1-2
好好合作 - Nathan

全部:感谢 G. Grothendieck 指出 RSQLite 1.1-1 引入的不兼容问题。如对答案的评论所述，将 RSQLite 降级到 1.0.0:

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