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

From: Chris Stubben <stubben_at_lanl.gov>
Date: Tue, 05 Jun 2007 15:39:16 +0000 (UTC)

> 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. Received on Tue 05 Jun 2007 - 15:48:52 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 - 18:32:02 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.