在R DBI中绑定变量 [英] Bind variables in R DBI

问题描述

在R的 DBI 包中，我没有找到使用绑定变量的工具。我找到了一个文档（2002年的原始版本），说到关于绑定变量，也许DBI可以在未来的某个时间实现这个功能，但它看起来像到目前为止已被撤消。

In R's DBI package, I'm not finding a facility for using bound variables. I did find a document (the original vignette from 2002) that says about bound variables, "Perhaps the DBI could at some point in the future implement this feature", but it looks like so far that's left undone.

R中的人使用替代品是什么？只是将字符串连接到SQL？这有一些明显的问题，安全&

What do people in R use for a substitute? Just concatenate strings right into the SQL? That's got some obvious problems for safety & performance.

编辑：

以下是占位符工作原理的示例：

Here's an example of how placeholders could work:

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

不是一个非常精心设计的界面，但是想法是你可以使用 bind 的值，驱动程序处理转义的细节（如果基础API不'本地的句柄绑定变量），而无需调用者重新实现它。[/ p>

That's not a very well-thought-out interface, but the idea is that you can use a value for bind and the driver handles the details of escaping (if the underlying API doesn't handle bound variables natively) without the caller having to reimplement it [badly].

推荐答案

只是在googling for rsqlite和dbgetpreparedquery之后，似乎在最新版本的rsqlite中，您可以运行带有绑定变量的SELECT查询。我只是运行以下：

For anyone coming to this question like I just did after googling for rsqlite and dbgetpreparedquery, it seems that in the latest version of rsqlite you can run a SELECT query with bind variables. I just ran the following:

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

这是相对较快的（从450,000行表中选择2,000行），并且令人难以置信地非常有用。

This was relatively fast (selecting 2,000 rows out of a 450,000 row table) and is incredibly useful.

FYI。

这篇关于在R DBI中绑定变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！