CODEWITHSUNDEEP
sqlrpostgresqlrpostgresql
Corvus
Corvus

Reputation: 8049

How to use parameters with RPostgreSQL (to insert data)

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

insert into mytable (a,b,c) values ($1,$2,$3)

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

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...

Upvotes: 11

Views: 6713

Answers (3)

tshynik
tshynik

Reputation: 147

Here's a version using the DBI and RPostgres packages, and inserting multiple rows at once, since all these years later it's still very difficult to figure out from the documentation.

x <- data.frame(
  a = c(1:10),
  b = letters[1:10],
  c = letters[11:20]
)

# insert your own connection info
con <- DBI::dbConnect(
  RPostgres::Postgres(),
  dbname = '', 
  host = '', 
  port = 5432, 
  user = '',
  password = ''
)

RPostgres::dbSendQuery(
  con, 
  "INSERT INTO mytable (a,b,c) VALUES ($1,$2,$3);",
  list(
    x$a,
    x$b,
    x$c
  )
)

The help for dbBind() in the DBI package is the only place that explains how to format parameters:

The placeholder format is currently not specified by DBI; in the future, a uniform placeholder syntax may be supported. Consult the backend documentation for the supported formats.... Known examples are:

  • ? (positional matching in order of appearance) in RMySQL and RSQLite
  • $1 (positional matching by index) in RPostgres and RSQLite
  • :name and $name (named matching) in RSQLite

? is also the placeholder for R package RJDBC.

Upvotes: 5

Niels Kristian
Niels Kristian

Reputation: 8835

As of the latest RPostgreSQL it should work:

db_connection <- dbConnect(dbDriver("PostgreSQL"), dbname = database_name,
                   host = "localhost", port = database_port, password=database_user_password,
                   user = database_user)
qry = "insert into mytable (a,b,c) values ($1,$2,$3)"
dbSendQuery(db_connection, qry, c(1, "some string", "some string with | ' "))

Upvotes: 6

LauriK
LauriK

Reputation: 1929

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

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

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'

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

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)

Same naturally works with variables as well:

> 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.

Upvotes: 8

Related Questions