1

I've a data.frame in R - matches with some 1000+ rows

names(matches)
[1] "name"  "c_id"  "fname" "lname" "address" "zip_code"    "Weight"

nrow(matches)
[1] 1253

I have a postgresql database table - list_m with following columns

db_name, db_cid, db_weight, processing_status, request_id, fname, mname, lname, etc.

I want to udpate the values of only few columns (db_c_id, db_weight and processing status) in the table using values from the data.frame.

As of now, I am looping over the data.frame to create the update queries and then run the queries.

for(row in 1:nrow(matches) {   
    query1 <- paste0(query1, "UPDATE list_m SET db_name = ",matches$name[row],", db_weight = ",matches$weight[row],",  processing_status = 'MATCHED'
 WHERE request_id=111 AND db_c_id = '", matches$c_id[row], "';")
}

so it basically creates a query1 variable with

 UPDATE list_m SET db_name = 'HILLARY', db_weight = 51.41, processing_status = 'MATCHED' WHERE request_id=111 AND db_c_id = '1015310246';

 UPDATE list_m SET db_name = 'SANDERS', db_weight = 45.16, processing_status = 'MATCHED' WHERE request_id=111 AND db_c_id = '1015120982';

 ...

 ...

 ...

 UPDATE list_m SET db_name = 'OBAMA', db_weight = 67.11, processing_status = 'MATCHED' WHERE request_id=111 AND db_c_id = '1015110111'; 

 UPDATE list_m SET db_name = 'TRUMP', db_weight = 41.22, processing_status = 'MATCHED' WHERE request_id=111 AND db_c_id = '1013024634';

which will then be executed using

dbSendStatement(con, query1)

What I would like is to do this by parameterizing the values.. something like

query2 <- "UPDATE list_m SET db_name=?,db_weight=?,processing_status='MATCHED' WHERE request_id=111 and db_c_id=?";

dbSendStatement(con, query2, matches$name, matches$weight, matches$c_id)

this statement should execute for each row of matches data.frame.

plannapus
  • 18,529
  • 4
  • 72
  • 94
Avinash Sonee
  • 1,701
  • 2
  • 15
  • 17
  • con is the db connection string: > drv <- dbDriver("PostgreSQL") > con <- dbConnect(drv, dbname=config$db$name,host=config$db$host,port=config$db$port,user=config$db$user,password=config$db$pass ) – Avinash Sonee Dec 21 '16 at 14:24

1 Answers1

3

This could be closely approximated using sprintf

sql_string <- "UPDATE list_m SET db_name = %s, db_weight = %s,  processing_status = 'MATCHED' WHERE request_id=111 AND db_c_id = '%s';"

dbSendStatement(con, paste(sprintf(sql_string, matches$name, matches$weight, matches$c_id), collapse=""))
manotheshark
  • 4,297
  • 17
  • 30
  • @Parfait changed answer to approximate parameters using `sprintf` – manotheshark Dec 21 '16 at 17:10
  • I thought your approach was great. Just needed to adhere to Postgres syntax and advise OP to export dataframe as temp table, altogether avoiding `for` loop. – Parfait Dec 21 '16 at 17:32
  • This approach is great! Would it be possible with named parameters instead of just relying on the parameter order? – SunWuKung Nov 27 '20 at 18:06
  • 1
    @SunWuKung the command could be wrapped in a function which would make it possible to used named parameters. However, the function will revert back to parameter order if the parameter names are not used when calling the function. – manotheshark Nov 30 '20 at 14:27