R help - Feb 2007 - Examples on how to READ/WRITE to database using R-Project

Hi,

 

We are working on a project on forecast modeling and would like to know if
there are any examples on how to READ/WRITE to a database (e.g. PostgreSQL)
using R-Project. I do have a sample R Script which takes input as files from
a directory and writes back output files to a directory.  I would like to
convert this script to read from a database and write back the output to  a
database.

 

Thanks,

 

- Venkatesh Mantha


	[[alternative HTML version deleted]]

> We are working on a project on forecast modeling and would like to know if
> there are any examples on how to READ/WRITE to a database (e.g. 
> PostgreSQL)
> using R-Project. I do have a sample R Script which takes input as files 
> from
> a directory and writes back output files to a directory.  I would like to
> convert this script to read from a database and write back the output to 
> a
> database.
> - Venkatesh Mantha
You probably want to install the RODBC package.  Then,
library(RODBC)
?RODBC

Also, the the relational database section of the R Data Import/Export Manual 
is worth reading:
http://cran.r-project.org/doc/manuals/R-data.html

Hi,

On 2/21/07, Venkatesh Mantha <venkatesh.mantha@pluralsoft.com>
wrote:
>
> We are working on a project on forecast modeling and would like to know if
> there are any examples on how to READ/WRITE to a database (e.g.
> PostgreSQL)


did you have a look at the Manual "R Data Import/Export"? I have to
admit
that I have not worked with PostgreSQL but with MS Access (and SQLite).
Databases such as PostgreSQL, Access, ... can be accessed via the package
RODBC.

Enjoy!
There might be a few difficulties in the beginning but then you don't want
to miss the connections to databases anymore!

Best,
Roland

	[[alternative HTML version deleted]]

You haven't told us your OS.
 
Here's how I do it on Win2K with MySQL.  PostgreSQL should be similar.  If
you are on Linux, you should read this:
http://help.nceas.ucsb.edu/index.php/R:_Data_input/output
 
#download and install ODBC
driverhttp://dev.mysql.com/downloads/connector/odbc/3.51.html
#Create ODBC connectionhttp://www.simongibson.com/intranet/odbc/
 
library(RODBC)
#read from dbchan <- odbcConnect("MySQL", uid="root",
pwd="password")  #uses default databasedata1 <- sqlQuery(chan,
"select * from tablename")close(chan)data1
#write to dbchan <- odbcConnect("MySQL", uid="root",
pwd="password")  #uses default databasesqlSave(chan,
data1,tablename="tablename",append=T,addPK=F,safer=T,verbose=T,rownames
= F)close(chan)
> From: venkatesh.mantha@pluralsoft.com> To: rbaer@atsu.edu;
rbaer@atsu.edu; r-help@stat.math.ethz.ch> Date: Wed, 21 Feb 2007 10:43:34
-0500> Subject: Re: [R] Examples on how to READ/WRITE to database using
R-Project> > Thanks very much for your reply. We am new to "R"
and actually my main> interest is making sure that we can develop a R script
that can efficiently> read/write to the database. Today we generate text
files from the database,> run R in batch mode on these files and load the
text files back into the> database. Even though this process works, given the
amount of data we expect> to have and the forecasting results we expect to
see this could be very time> consuming.> > Our goal is to eliminate
this additional I/O of reading/writing to files and> would like to read/write
from/to the database in order to save time and have> a better process
architecture.> > I did read the manual and couldn't make much out of
it. I was looking to see> if somebody has already done this so that I could
use that to Jump start our> project.> > Regards,> > - Venkatesh
Mantha> > -----Original Message-----> From: Robert Baer
[mailto:rbaer@atsu.edu] > Sent: Wednesday, February 21, 2007 10:34 AM> To:
Venkatesh Mantha; r-help@stat.math.ethz.ch> Subject: Re: [R] Examples on how
to READ/WRITE to database using R-Project> > > > We are working on a
project on forecast modeling and would like to know if> > there are any
examples on how to READ/WRITE to a database (e.g. > > PostgreSQL)> >
using R-Project. I do have a sample R Script which takes input as files >
> from> > a directory and writes back output files to a directory. I
would like to> > convert this script to read from a database and write
back the output to > > a> > database.> > - Venkatesh
Mantha> You probably want to install the RODBC package. Then,>
library(RODBC)> ?RODBC> > Also, the the relational database section of
the R Data Import/Export Manual> > is worth reading:>
http://cran.r-project.org/doc/manuals/R-data.html> >
______________________________________________> R-help@stat.math.ethz.ch
mailing list> https://stat.ethz.ch/mailman/listinfo/r-help> PLEASE do read
the posting guide http://www.R-project.org/posting-guide.html> and provide
commented, minimal, self-contained, reproducible code.
_________________________________________________________________
Soyez parmi les premiers à essayer Windows Live Mail.

4911fb2b2e6d
	[[alternative HTML version deleted]]