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

From: bogdan romocea <br44114_at_gmail.com>
Date: Tue, 05 Jun 2007 14:29:26 -0400


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.
>
> Bruce
>
>
> 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.
>



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:37:09 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 - 20:33:34 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.