问题描述
我最近升级了R，RSQLite和sqldf(下面的版本).
I've recently upgraded R, RSQLite, and sqldf (versions below).
通常:
sqldf('create table foo as select * from bar', db = 'test.db')
应该在附加的sqlite数据库中创建一个名为"foo"的表，并使用数据框"bar"(如果存在的话)来加载新表.
should create a table called 'foo' in an attached sqlite database, using data frame 'bar' if it exists to load the new table.
相反，我收到了没有这样的表"错误，并且当我查看数据库时，同时创建了"foo"表和"bar"表.
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.
可复制的示例:
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.
推荐答案
UPDATE:RSQLite和sqldf的最新版本不存在此问题解决的不兼容问题IE.:sqldf_0.4-10 RSQLite_1.1-2一起工作-Nathan
UPDATE: newest versions of RSQLite and sqldf do not have the incompatibility issues addressed by this questioni.e.:sqldf_0.4-10 RSQLite_1.1-2work together well - Nathan
全部:感谢G. Grothendieck提出的RSQLite 1.1-1引入的不兼容问题的指针.如答案注释中所述，将RSQLite降级为1.0.0:
All: Thanks to G. Grothendieck for pointers to incompatibility issues introduced by RSQLite 1.1-1. As stated in the comment to the answer, downgrade RSQLite to 1.0.0:
devtools::install_url("https://cran.r-project.org/src/contrib/Archive/RSQLite/RSQLite_1.0.0.tar.gz")
这篇关于sqldf:根据数据帧错误创建表:“无此表".和创建两个表而不是一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！