Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

update table in postgresql database through r

Tags:

r

postgresql

How do I update data in a postgresql db through R with new data?

I've tried

dbGetQuery(con,"UPDATE table SET column1=:1,column2=:2, column3=:3 
              where id=:4", data=Rdata[,c("column1", "column3", "column3","id")])

I also tried with the colons replaced with $ but that didn't work either. I keep getting:

Error in postgresqlExecStatement(conn, statement, ...) : 
unused argument(s)
like image 929
cconnell Avatar asked Feb 26 '13 21:02

cconnell


People also ask

How do I connect to a PostgreSQL database using R?

Steps to Connect R & PostgreSQL using RPostgreSQLStep 1: Install the RPostgreSQL Package. Step 2: Enter your PostgreSQL Credentials. Step 3: Establish R & PostgreSQL Connection using RPostgreSQL. Step 4: Run and Test Queries using RPostgreSQL.

How do I fill a table in PostgreSQL?

There are generally three methods in PostgreSQL with which you can fill a table with data: Use the INSERT INTO command with a grouped set of data to insert new values. Use the INSERT INTO command in conjunction with a SELECT statement to insert existing values from another table.


2 Answers

I figured it out using:

update <- function(i) {
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname="db_name", host="localhost", port="5432", user="chris", password="password")
txt <- paste("UPDATE data SET column_one=",data$column_one[i],",column_two=",data$column_two[i]," where id=",data$id[i])
dbGetQuery(con, txt)
dbDisconnect(con)
}


registerDoMC()

foreach(i = 1:length(data$column_one), .inorder=FALSE,.packages="RPostgreSQL")%dopar%{
update(i)
}
like image 71
cconnell Avatar answered Sep 23 '22 05:09

cconnell


At least the RODBC has a specific function sqlUpdate:

sqlUpdate updates the table where the rows already exist. Data frame dat should contain columns with names that map to (some of) the columns in the table

See http://cran.r-project.org/web/packages/RODBC/RODBC.pdf

like image 20
jnas Avatar answered Sep 19 '22 05:09

jnas