代码之家 › 专栏 › 技术社区 › Ken Williams Dirk is no longer here

在r dbi中绑定变量

r database

Ken Williams Dirk is no longer here · 技术社区 · 15 年前

在R的 DBI 包,我找不到使用绑定变量的工具。我确实找到了一个文档(2002年的原始小插曲),其中提到了绑定变量,“也许dbi在将来的某个时候可以实现这个特性”,但是到目前为止,这看起来还没有完成。

R中的人用什么来代替?只是将字符串直接连接到sql中?在安全和性能方面有一些明显的问题。

编辑:

以下是占位符如何工作的示例:

query <- "SELECT numlegs FROM animals WHERE color=?"
result <- dbGetQuery(caseinfo, query, bind="green")

这不是一个经过深思熟虑的界面,但其思想是您可以使用 bind 驱动程序处理转义的详细信息(如果底层api不以本机方式处理绑定变量),而调用者不必[严重地]重新实现它。

3 回复 | 直到 13 年前

user1076 13 年前

对于像我在搜索rsqlite和dbgetpreparedquery之后遇到这个问题的人来说,似乎在rsqlite的最新版本中,您可以使用bind变量运行select查询。我刚刚运行了以下命令:

query <- "SELECT probe_type,next_base,color_channel FROM probes WHERE probeid=?"
probe.types.df <- dbGetPreparedQuery(con,que,bind.data=data.frame(probeids=ids))

这是相对较快的(从450000行表中选择2000行),并且是 难以置信地 有用的。

FYI。

seth 15 年前

下面是rsqlite中当前支持的绑定参数。你说得对,目前还不支持选择,但没有充分的理由,我想补充支持它。

如果你想黑客,你可以得到所有的此处是与dbi相关的包:

use --user=readonly --password=readonly

https://hedgehog.fhcrc.org/compbio/r-dbi/trunk
https://hedgehog.fhcrc.org/compbio/r-dbi/trunk/DBI
https://hedgehog.fhcrc.org/compbio/r-dbi/trunk/SQLite/RSQLite

我喜欢收到补丁,特别是如果它们包括测试和文档。请统一区分。我真的做了我所有的使用git进行开发,因此最好是创建say的git克隆 rsqlite然后以 git format-patch -n git-svn..

无论如何,这里有一些例子:

library("RSQLite")

make_data <- function(n)
{
    alpha <- c(letters, as.character(0:9))
    make_key <- function(n)
    {
        paste(sample(alpha, n, replace = TRUE), collapse = "")
    }
    keys <- sapply(sample(1:5, replace=TRUE), function(x) make_key(x))
    counts <- sample(seq_len(1e4), n, replace = TRUE)
    data.frame(key = keys, count = counts, stringsAsFactors = FALSE)
}

key_counts <- make_data(100)


db <- dbConnect(SQLite(), dbname = ":memory:")

sql <- "
create table keys (key text, count integer)
"

dbGetQuery(db, sql)

bulk_insert <- function(sql, key_counts)
{
    dbBeginTransaction(db)
    dbGetPreparedQuery(db, sql, bind.data = key_counts)
    dbCommit(db)
    dbGetQuery(db, "select count(*) from keys")[[1]]
}

##  for all styles, you can have up to 999 parameters

## anonymous
sql <- "insert into keys values (?, ?)"
bulk_insert(sql, key_counts)


## named w/ :, $, @
## names are matched against column names of bind.data

sql <- "insert into keys values (:key, :count)"
bulk_insert(sql, key_counts[ , 2:1])

sql <- "insert into keys values ($key, $count)"
bulk_insert(sql, key_counts)

sql <- "insert into keys values (@key, @count)"
bulk_insert(sql, key_counts)

## indexed (NOT CURRENTLY SUPPORTED)
## sql <- "insert into keys values (?1, ?2)"
## bulk_insert(sql)

Ken Williams Dirk is no longer here 15 年前

嘿嘿-我刚刚发现rsqlite,这就是我在本例中使用的,确实有绑定变量支持:

http://cran.r-project.org/web/packages/RSQLite/NEWS

查看有关的条目 dbSendPreparedQuery() 和 dbGetPreparedQuery() .

所以从理论上讲,这就改变了这种肮脏:

df <- data.frame()
for (x in data$guid) {
  query <- paste("SELECT uuid, cites, score FROM mytab WHERE uuid='",
                 x, "'", sep="")
  df <- rbind(df, dbGetQuery(con, query))
}

进入这个:

df <- dbGetPreparedQuery(
     con, "SELECT uuid, cites, score FROM mytab WHERE uuid=:guid", data)

不幸的是,当我真的尝试的时候,它似乎只是为了 INSERT 声明之类的,不是为了 SELECT 语句,因为我得到一个错误: RS-DBI driver: (cannot have bound parameters on a SELECT statement) .

提供这种能力将是非常棒的。

下一步是将它提升到dbi本身,以便所有的dbs都能利用它,并提供一个默认实现,就像我们现在自己做的那样将它粘贴到字符串中。