I've recently upgraded R, RSQLite, and sqldf (versions below).
Normally:
sqldf('create table foo as select * from bar', db = 'test.db')
should create a table called 'foo' in an attached sqlite database, using data frame 'bar' if it exists to load the new table.
Instead, I'm getting a 'no such table' error, and also when I look at the database there are both 'foo' and 'bar' tables created.
Reproducible 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 produces 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.
dbWriteTable()and leavesqldf()to only manipulate R dfs? – Prosaicdevtools::install_url("https://cran.r-project.org/src/contrib/Archive/RSQLite/RSQLite_1.0.0.tar.gz")– Hebert?dbWriteTableand?dbRemoveTablesays thatnameis "[a] character string specifying a DBMS table name", it says nowhere thatnameshould be quoted, this is whatsqldfseems to be doing internally. I'd argue that sqldf operates out of spec here. That said, I'm releasing RSQLite 1.1-2 very soon to improvesqldfcompatibility. – AllaallahsessionInfo()fails also with RSQLite 1.0.0. Please confirm. The following works:sqldf('select * from mtcars limit 1', dbname = mydb). – Allaallahsqldf("select * from mtcars limit 1", dbname = "x.db")works under RSQLite 1.0.0 whether or not x.db exists but fails in both cases under 1.1-1. Withoutdbname = "x.db"it works on both RSQLite 1.1-1 and 1.0.0. – Hebert