mysql query output

Question

[kani.kanchan]

Hi Everyone,

I am trying to run a mysql query and save output into a text file. The command works fine for 10 SNPs and the output file has entries for all 10 SNPs, however when I try to run the same for large list (100 K) of SNPs the output file only contains the entry for the last SNP only.

My command -

for i in $(cat /path/to/directory/listofSNPs.txt); do echo "select chrom,chromStart+1,name,alleles from snp150 where (name= \"$i\")" | mysql -N --user=genome --host=genome-mysql.cse.ucsc.edu -A -P 3306 -D hg38 done > '/path/to/directory/output.txt'

I would appreciate any advice on how can I do this?

Thanks, Kanika

[Pierre Lindenbaum]

, however when I try to run the same for large list (100 K) of SNPs

why don't you use A: Need help with getting chromosomal positions of SNPs (your previous question...)

[kani.kanchan]

I tried to run this command for some SNPs and the code works well. But when I ran it for larger list it run indefinitely.

[GenoMax]

If you have 100K queries then you need to account for that number. Surely your patience ran out before the queries completed.

Answer 1

[Pierre Lindenbaum]

faster:

awk '{printf("select chrom,chromStart+1,name,alleles from snp150 where name= \"%s\";\n",$1);}' listofSNPs.txt | \
mysql -N --user=genome --host=genome-mysql.cse.ucsc.edu -A -P 3306 -D hg38 > /path/to/directory/output.txt

Answer 2

[GenoMax]

Use append which is designated by >> instead of a single > when writing the file out.