我最近升级了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.
最佳答案
更新: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")
关于r - sqldf : create table from data frame error: “no such table” .和两个表而不是一个表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41511901/