Skip to content

R and PostgreSQL - Writing data?

5 messages · James David Smith, Sean Davis, Denis Mukhin

#
Dear all,

Sorry if this isn't quite the right place, but it's the first time
I've posted here. My issue is to do with writing to a PostgreSQL
database from within R. My situation is best explained by some R code
to start:

#Connect to the database
con <- dbConnect(PostgreSQL(), user="postgres", password="password",
dbname="database")

#Get some data out of the database.
ucam_no2$original_data <- dbGetQuery(con, "select ucam_no2 FROM table")

This returns say 10000 rows of data, but there is only data in about
half of those rows. What I want to do is interpolate the missing data
so I do this:

#Generate some data
ucam_no2$interpolated_data <- na.approx(ucam_data$ucam_no2, na.rm = FALSE)

This works well and I now have 10000 rows of data with no empty cells.
I now want to write this back into my PostgresSQL database. Into the
same row that I took the data from in the first place. But I don't
know how. I can write to a new table with something like the below,
but what I'd really like to do is put the data back into the table I
got it from.

# Try to write the data back
dbWriteTable(con, "new_data", ucam_no2$interpolated_data)

Thanks

James
#
On Fri, Sep 28, 2012 at 10:14 AM, James David Smith
<james.david.smith at gmail.com> wrote:
Hi, James.

You'll need to look into doing a SQL UPDATE.  That is the standard way
to "put data back into the table I got it from".

Sean
#
Hi Sean,

Thanks for the reply. I'm familiar with UPDATE queries when working in
PostgreSQL, but not from within R. Would it look something like this?

dbWriteTable(con, " UPDATE table SET ucam_no2 = 'ucam_no2$interpolated_data' ")

My problem is how to get the R data 'within' my SQL statement I think.

Thanks

James
On 28 September 2012 15:19, Sean Davis <sdavis2 at mail.nih.gov> wrote:
SendSave NowDiscardDraft autosaved at 15:36 (0 minutes ago)
33% full
Using 3.4 GB of your 10.1 GB
?2012 Google - Terms & Privacy
Last account activity: 50 minutes ago
Details
People (2)
Sean Davis	
Add to circles

Show details
Ads ? Why these ads?
Big Data Too Slow?
Real-Time Analytics for Big Data. Visual Drag & Drop UI. Quick & Easy
PentahoBigData.com
Talend Open Source ESB
Open Source ESB Based on Apache CXF and Apache Camel. Free Download!
www.talend.com/Free_ESB_Software
Warp I/O for SQL Server
Speed SQL Server performance 3x Faster I/O, reduced storage
www.confio.com/warp-io
Storage Container Sussex
Ex-Shipping Containers Sale & Hire Storage Container 0800 043 6311
www.CsShippingContainers.co.uk
More about...
MS Access Database SQL ?
Database ?
Excel Database Query ?
Oracle Database Problems ?
#
On Fri, Sep 28, 2012 at 10:36 AM, James David Smith
<james.david.smith at gmail.com> wrote:
To do an update, you'll need to loop through your data.frame and issue
a dbSendQuery().  To create the SQL string, I often use something
like:

sprintf("UPDATE originalTable SET ucam_no2=%f WHERE originalTable.id = %d",....)

You can't do this in one step, unfortunately.  This is how UPDATE
works and has nothing to do with R.

Sean
#
James,

I have never tried RPostgreSQL before but in ROracle which is also a DBI based interface you can do something like this:

library(ROracle)
con <- dbConnect(Oracle(), "scott", "tiger")
dbGetQuery(con, "create table foo as select * from emp")

dat <- dbGetQuery(con, "select * from foo")
dat$SAL <- dat$SAL*10
dbGetQuery(con, "update foo set sal = :1 where empno = :2",
           data = dat[,c("SAL","EMPNO")])
dbCommit(con)
dbGetQuery(con, "select * from foo")

dbGetQuery(con, "drop table foo purge")
dbDisconnect(con)

Denis

-----Original Message-----
From: Sean Davis [mailto:sdavis2 at mail.nih.gov] 
Sent: Friday, September 28, 2012 11:43 AM
To: James David Smith
Cc: r-sig-db at r-project.org
Subject: Re: [R-sig-DB] R and PostgreSQL - Writing data?
On Fri, Sep 28, 2012 at 10:36 AM, James David Smith <james.david.smith at gmail.com> wrote:
To do an update, you'll need to loop through your data.frame and issue a dbSendQuery().  To create the SQL string, I often use something
like:

sprintf("UPDATE originalTable SET ucam_no2=%f WHERE originalTable.id = %d",....)

You can't do this in one step, unfortunately.  This is how UPDATE works and has nothing to do with R.

Sean
_______________________________________________
R-sig-DB mailing list -- R Special Interest Group R-sig-DB at r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-db