Re: [R] RMySQL question, sql with R vector or list

From: Waverley <waverley.paloalto_at_gmail.com>
Date: Tue, 05 Jun 2007 11:49:43 -0700

Thanks for the tip. But this is different from what I asked.

I know how to export the R object to a MySQL table. But what I don't know how I can use R objects as variables in constructing SQLs to integrrogate MYSQL database. Chris's suggestion using paste to construct is one which basically export out R object as strings into SQLs then send to MYSQL. This is a good suggestion. But is there any other way to do this which would achieve the same effect without get the variable content out as strings? This would help to better integrate the code and integrate R data structure with database integrrogation.

Thanks much for the help in advance.

On 6/5/07, bogdan romocea <br44114_at_gmail.com> wrote:
>
> With regards to your concern - export the R object to a MySQL table
> (the RMySQL documentation tells you how), then run an inner join. Or
> if the table to query isn't that big, pull it in R and subset it with
> %in%. You could use system.time() to see which runs faster.
>
>
> > -----Original Message-----
> > From: r-help-bounces_at_stat.math.ethz.ch
> > [mailto:r-help-bounces_at_stat.math.ethz.ch] On Behalf Of Waverley
> > Sent: Tuesday, June 05, 2007 1:49 PM
> > To: Chris Stubben
> > Cc: r-help_at_stat.math.ethz.ch
> > Subject: Re: [R] RMySQL question, sql with R vector or list
> >
> > Thanks Chris.
> >
> > I think this should work. I have one more question regarding
> > this. Is that
> > possible to write some PL/SQL scripts integrated inside R, it
> > is the same
> > token like I have asked in my previous question. In this
> > way, native R data
> > structures can be passed to the MYSQL data base directly to
> > interrogate
> > dynamically, rather than statically like using paste. One
> > concern using
> > paste to construct the SQL command is this: what about if the
> > ID list in
> > your sample becomes very large, is this a problem to
> > construct this way?
> >
> > I will try to follow your advice but I hope someone on the
> > mailing list can
> > teach me how to integrate R data structure with MYSQL like PL/SQL.
> >
> > Thanks much.
> >
> >
> >
> >
> > On 6/5/07, Chris Stubben <stubben_at_lanl.gov> wrote:
> > >
> > >
> > > > I am trying to write a RMySQL sql script inside R such
> > that part of the
> > > SQL
> > > > would be R list or vector. For example, I want to select
> > * from Atable
> > > > where ID would equal to a members of R list or vector of "1, 2,
> > > 3". Here
> > > > the ID list was generated inside R and then try to feed
> > to mysql to call
> > > to
> > > > get additional data sets.
> > > >
> > >
> > >
> > > You could pass a comma-separated list of IDs to the sql IN operator
> > >
> > >
> > > ## in MySQL
> > >
> > > CREATE table tmp (id int, name char(1));
> > > insert into tmp values (1, "A"), (2, "B"), (3, "C"), (4,
> > "D"), (5, "E");
> > >
> > >
> > >
> > > ### in R
> > >
> > >
> > > library(RMySQL)
> > >
> > > con <- dbConnect("MySQL", dbname="test" )
> > >
> > >
> > > id.in<-function(ids)
> > > {
> > > dbGetQuery(con, paste("select * from tmp
> > > where id IN (", paste(ids,collapse=","), ")") )
> > > }
> > >
> > >
> > >
> > > id.in(2:4)
> > > id name
> > > 1 2 B
> > > 2 3 C
> > > 3 4 D
> > >
> > >
> > > ## simple lists also work
> > >
> > > id.in(list(1,4,5))
> > > id name
> > > 1 1 A
> > > 2 4 D
> > > 3 5 E
> > >
> > >
> > > Chris
> > >
> > > ______________________________________________
> > > R-help_at_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.
> > >
> >
> >
> >
> > --
> > Waverley @ Palo Alto
> >
> > [[alternative HTML version deleted]]
> >
> > ______________________________________________
> > R-help_at_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.
> >
>

-- 
Waverley @ Palo Alto

	[[alternative HTML version deleted]]

______________________________________________
R-help_at_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.
Received on Tue 05 Jun 2007 - 18:55:05 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Tue 05 Jun 2007 - 19:31:24 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.