如何在RPostgreSQL中使用参数（插入数据） [英] How to use parameters with RPostgreSQL (to insert data)

问题描述

我正在尝试使用 RPostgreSQL 将数据插入到预先存在的PostgreSQL表中，但我无法弄清楚SQL参数（准备好的语句）的语法。

I'm trying to insert data into a pre-existing PostgreSQL table using RPostgreSQL and I can't figure out the syntax for SQL parameters (prepared statements).

例如假设我想执行以下操作

E.g. suppose I want to do the following

插入mytable（a，b，c）值（$ 1，$ 2，$ 3）

如何指定参数？ dbSendQuery 似乎不理解是否只是将参数放在 ... 。

How do I specify the parameters? dbSendQuery doesn't seem to understand if you just put the parameters in the ....

我发现 dbWriteTable 可用于转储整个表，但不能让您指定列（所以不好默认设置等）。而且无论如何，一旦我在那里获得数据，我将需要为其他查询知道这一点（所以我想这并不是特定于插入的）！

I've found dbWriteTable can be used to dump an entire table, but won't let you specify the columns (so no good for defaults etc.). And anyway, I'll need to know this for other queries once I get the data in there (so I suppose this isn't really insert specific)!

只是缺少明显的东西...

Sure I'm just missing something obvious...

推荐答案

出于相同的原因，我一直在寻找相同的东西，这就是安全性。

I was looking for the same thing, for the same reasons, which is security.

显然dplyr软件包具有您感兴趣的功能。虽然几乎没有记录，但确实存在。在此小插图中向下滚动到 Postgresql： http：//cran.r-project。 org / web / packages / dplyr / vignettes / databases.html

Apparently dplyr package has the capacity that you are interested in. It's barely documented, but it's there. Scroll down to "Postgresql" in this vignette: http://cran.r-project.org/web/packages/dplyr/vignettes/databases.html

总而言之，dplyr提供了sql（）和escape（）函数，可以将它们组合使用产生参数化查询。来自DBI包的SQL（）函数似乎以完全相同的方式工作。

To summarize, dplyr offers functions sql() and escape(), which can be combined to produce a parametrized query. SQL() function from DBI package seems to work in exactly same way.

> sql(paste0('SELECT * FROM blaah WHERE id = ', escape('random "\'stuff')))
<SQL> SELECT * FROM blaah WHERE id = 'random "''stuff'

它返回 sql和 character类的对象，因此您可以将其传递给

It returns an object of classes "sql" and "character", so you can either pass it on to tbl() or possibly dbSendQuery() as well.

escape（）函数也可以正确处理矢量，我发现它最有用：

The escape() function correctly handles vectors as well, which I find most useful:

> sql(paste0('SELECT * FROM blaah WHERE id in ', escape(1:5)))
<SQL> SELECT * FROM blaah WHERE id in (1, 2, 3, 4, 5)

同理自然以及变量：

> tmp <- c("asd", 2, date())
> sql(paste0('SELECT * FROM blaah WHERE id in ', escape(tmp)))
<SQL> SELECT * FROM blaah WHERE id in ('asd', '2', 'Tue Nov 18 15:19:08 2014')

现在将查询放在一起会更安全。

I feel much safer now putting together queries.

这篇关于如何在RPostgreSQL中使用参数（插入数据）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！