[R-sig-DB] R and PostgreSQL - Writing data?

[Denis Mukhin]

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 using mail.nih.gov] 
Sent: Friday, September 28, 2012 11:43 AM
To: James David Smith
Cc: r-sig-db using 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 using gmail.com> wrote:
> 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.

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


>
> On 28 September 2012 15:19, Sean Davis <sdavis2 using mail.nih.gov> wrote:
>> On Fri, Sep 28, 2012 at 10:14 AM, James David Smith 
>> <james.david.smith using gmail.com> wrote:
>>> Dear all,
>>>
>>> Sorry if this isn't quite the right place, but it's the first time
> 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 »
>
>>> 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)
>>
>> 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

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