R help - Jun 2007 - RMySQL question, sql with R vector or list

Hi,

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.

Can someone on the list help?

Thanks.

-- 
Waverley @ Palo Alto

	[[alternative HTML version deleted]]

> 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

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