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

[James David Smith]

Hi Dirk,

Would be interested to know what you mean by 'expand the strings
explicitly' please? Would that help me? I want to be able to write
data from a dataframe into an existing PostgresSQL table, matching a
column to a variable in the dataframe (e.g. like the query I posted).

Thanks

James

On 30 November 2012 15:26, Dirk Eddelbuettel <edd using debian.org> wrote:
>
> On 30 November 2012 at 15:05, James David Smith wrote:
> | Hi all,
> |
> | Sorry for the thread re-activation. I was wondering if anyone has
> | successfully used the syntax below with the library RPostgreSQL?
>
> Nope.
>
> I always expand the strings explicitly. It would be news to me of that
> worked.  Good news, for sure, but still news...
>
> Dirk
>
>
> | 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
> |
> | _______________________________________________
> | 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
>
> --
> Dirk Eddelbuettel | edd using debian.org | http://dirk.eddelbuettel.com