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

From: Waverley <waverley.paloalto_at_gmail.com>
Date: Tue, 05 Jun 2007 10:48:32 -0700

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.
Received on Tue 05 Jun 2007 - 17:54:57 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.