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

[James David Smith]

Hi all,

Sorry for the thread re-activation. I was wondering if anyone has
successfully used the syntax below with the library RPostgreSQL?

dbGetQuery(con, "update foo set sal = :1 where empno = :2",
           data = dat[,c("SAL","EMPNO")])

I've been messing about with it but can't get it to work. I get the error:

Error in postgresqlQuickSQL(conn, statement, ...) :
  unused argument(s) (data = list(bc = c(NA, NA, NA etc.

Thanks

James



On 28 September 2012 17:13, Denis Mukhin <denis.x.mukhin using oracle.com> wrote:
> 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
>
> _______________________________________________
> 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