[R] using sprintf to pass a variable to a RMySQL query

[alison waller]

Hi all,

Thanks for help with the paste and sprintf syntax.

So I've decided to use paste and or sprintf.  'gsubfn' looks like a
great package but unfortunately I've had problems installing it, as I
don't think it likes the version of tcltk that is installed.  I'm
working on a few unix clusters with many computers and there seems to be
problems with different versions of R and different versions of the
packages on different computers.

So, the other problem is that I want to rename the data.frames and names
of the output jpeg files resulting from the queries.  I've tried a few
different approaches but none seem to work, using sprintf and paste
turns the data frame into just a string of the name.

I have a complicated loop here as I'd like to do some summary output
after every 4 queries (ie. after MG1, MG 19, MG 37, MG 54) then I want
to start again and do for MG2, MG20 etc..

Here's my code below, there are probably error in the loop structure
that I can work out, but I need help with renaming the data frames based
on the parameters i and j

thanks

################################
i<-1
j<-1

for (i<=72 and j<=4){{

sqlcmd_ScaffLen<- paste("SELECT scaffold.length
FROM scaffold, scaffold2contig, contig2read
WHERE scaffold.scaffold_id=scaffold2contig.scaffold_id AND
scaffold2contig.contig_id=contig2read.contig_id AND
contig2read.read_id LIKE '%MG", i ,"%'", sep='')

sqlcmd_contigs<-paste("SELECT length FROM contig WHERE external_id LIKE
'%MG",i,"%'",sep='' )

sqlcmd_singletons<-paste("SELECT COUNT(*) FROM contig WHERE
read_count=1 AND external_id LIKE '%MG",i,"%'",sep='')

MGi_ScaffoldLen<-dbGetQuery(con,sqlcmd_ScaffLen)
MGi_ContigsLen<-dbGetQuery(con,sqlcmd_contigs)
MGi_SingletonsCount<-dbGetQuery(con,sqlcmd_singletons)

MGi_ScaffoldLen_Summ<-as.data.frame(c(summary(MGi_ScaffoldLen$length),MGi_SingletonsCount))
MGi_ContigsLen_Summ<-summary(MGi_ContigsLen$length)

write.table(MGi_ScaffoldLen_Summ,file="ScaffoldLen_SummStats.txt",append=TRUE,sep='\t')

write.table(MGi_ContigsLen_Summ,file="ContigsLen_SummStats.txt",append=TRUE,sep='\t')

i<-i+18
j<-j+1

}

############### Summary Plots For each Treatment ##################

jpeg(file=sprintf("Boxplots_%d.jpeg",i)
boxplot(MGi_ScaffoldLen$length,MG(i+18*j)_ScaffoldLen$length,MG(i+_ScaffoldLen$length,MG59_ScaffoldLen$length,Main="400spec_10virus")
dev.off()

jpeg(file=sprintf("Scaffold_histograms_%d.jpeg",i)
hist(MGi_ScaffoldLen$length)
hist(MG(i+j*18)_ScaffoldLen$length)
hist(MG(i+j*18_ScaffoldLen$length)
hist(MG(i+j*18_ScaffoldLen$length)

dev.off()

jpeg(file=sprintf("Contig_histograms_%d.jpeg",i)
hist(MGi_ContigsLen$length)
hist(MG(i+j*18)_ContigsLen$length)
hist(MG(i+j*18_ContigsLen$length)
hist(MG(i+j*18_ContigsLen$length)

dev.off()

j<-1
i<-2
}


On 03/08/10 21:02, Don MacQueen wrote:
> I always use paste()
>
> i <- 1
> sqlcmd_ScaffLen <- paste("SELECT scaffold.length
> FROM scaffold, scaffold2contig, contig2read
> WHERE scaffold.scaffold_id=scaffold2contig.scaffold_id AND
> scaffold2contig.contig_id=contig2read.contig_id AND
> contig2read.read_id LIKE '%MG", i ,"%'", sep='')
>
> That should create bits like
>    LIKE '%MG1%'
>    LIKE '%MG2%'
> and so on.
>
> You just have to get the nesting of the single and double quotes
> correct - the SQL requires single quotes, so use double quotes for the
> fixed character strings insidte paste(). That, and use sep='' to get
> rid of unwanted space characters.
>
> Using paste is also effective for constructs like
>   IN (3,4,5)
> or
>   IN ('a','b','c')
> though it can be necessary to nest one paste within another
>
> -Don
>
> At 2:06 PM +0100 3/8/10, alison waller wrote:
>> Hello,
>>
>> I am using RmySQL and would like to iterate through a few queries.
>>
>> I would like to use sprintf but I think I'm having problems mixing and
>> matching the sprintf syntax and the SQL regex.
>>
>> I have checked my sqlcmd and it works when I wan to match %MG1% but how
>> do I iterate for i 1-72?  Escape characters,?
>>
>> thanks in advance
>>
>> i<-1
>> sqlcmd_ScaffLen<-sprintf('SELECT scaffold.length
>> FROM scaffold,scaffold2contig,contig2read
>> WHERE scaffold.scaffold_id=scaffold2contig.scaffold_id AND
>> scaffold2contig.contig_id=contig2read.contig_id AND
>> contig2read.read_id LIKE
>> '%MG%s%' ,i)
>>
>> ========= Here is my vague error message
>>
>> Error: unexpected input in:
>>
>> ______________________________________________
>> R-help at r-project.org 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.
>
>