r - RSQLite 警告:“SQL 变量太多”
问题描述
我在使用 RSQLite 时遇到了以下错误,并且在诊断问题时遇到了问题:
Error in result_create(conn@ptr, statement) : too many SQL variables
数据库显示了正确的、固定数量 (24) 的列,应该有大约 190 行。因为许多行一次不适合 RAM,所以每个条目(行)都被迭代地附加。
不幸的是,它在第 99 行一直失败。但是,当我尝试仅将第 95 到 105 行输入数据库时,它可以工作。
# Doesn't work
samplesToAdd <- samplesToAdd[1:100, ]
newDatabase2 <- DBI::dbConnect(RSQLite::SQLite(),
"C:/Users/chase/Documents/GitHub/IDBac_App/inst/app/SpectraLibrary/z1.sqlite")
IDBacApp::addNewLibrary(samplesToAdd = samplesToAdd,
newDatabase = newDatabase2,
selectedIDBacDataFolder = selectedIDBacDataFolder)
Warning: Error in result_create: too many SQL variables
# Works
samplesToAdd <- samplesToAdd[95:105, ]
newDatabase2 <- DBI::dbConnect(RSQLite::SQLite(),
"C:/Users/chase/Documents/GitHub/IDBac_App/inst/app/SpectraLibrary/z1.sqlite")
IDBacApp::addNewLibrary(samplesToAdd = samplesToAdd,
newDatabase = newDatabase2,
selectedIDBacDataFolder = selectedIDBacDataFolder)
那么,当只有 24 个变量时,为什么会因为“变量太多”而失败?
解决方案
愚蠢的是,有一个全局分配的 for 循环。这具有每次迭代重新添加多个列的效果。SQLite 只是不附加额外的列,因此在插入太大之前不会失败。
然而,问题的令人费解的本质可以在下面的简化示例中看到。
library(RSQLite)
library(magrittr)
library(dplyr)
a <- mtcars[1, ]
b <- cbind(mtcars[1, ], mtcars[2, ])
> as_tibble(a)
# A tibble: 1 x 11
mpg cyl disp hp drat wt qsec vs am gear carb
* <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 21 6 160 110 3.9 2.62 16.5 0 1 4 4
> as_tibble(b)
Error: Columns `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb` must have unique names
# But "tibbble" wasn't used:
> b
mpg cyl disp hp drat wt qsec vs am gear carb mpg cyl disp hp drat wt qsec vs am gear carb
Mazda RX4 21 6 160 110 3.9 2.62 16.46 0 1 4 4 21 6 160 110 3.9 2.875 17.02 0 1 4 4
con <- DBI::dbConnect(RSQLite::SQLite(),
file.path(getwd(),"StackoverflowExample", "exw.sqlite"))
DBI::dbWriteTable(conn = con,
name = "IDBacDatabase", # SQLite table to insert into
a, # Insert single row into DB
append = TRUE, # Append to existing table
overwrite = FALSE) # Do not overwrite
DBI::dbWriteTable(conn = con,
name = "IDBacDatabase", # SQLite table to insert into
b, # Insert single row into DB
append = TRUE, # Append to existing table
overwrite = FALSE) # Do not overwrite
db <- dplyr::tbl(con, "IDBacDatabase")
db
# Source: table<IDBacDatabase> [?? x 11]
# Database: sqlite 3.22.0 [C:\Users\chase\Documents\StackoverflowExample\ex.sqlite]
mpg cyl disp hp drat wt qsec vs am gear carb
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 21 6 160 110 3.9 2.62 16.5 0 1 4 4
2 21 6 160 110 3.9 2.62 16.5 0 1 4 4
编辑要使插入失败:
b <- mtcars[1, ]
for(i in 1:1000){
b <- cbind(b, mtcars[2, ])
}