This is a test version of Biostars. For the public version, visit https://www.biostars.org.
mysql query output

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

snp
• 1,574 views • link
updated by GenoMax GenoMax • written by kani.kanchan • 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.

• 1 views • link
written by kani.kanchan • kani.kanchan

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

• 1 views • link
written by GenoMax GenoMax

2 answers

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
• 1 views • link
written by Pierre Lindenbaum Pierre Lindenbaum

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

• 1 views • link
written by GenoMax GenoMax

Log in to answer this question.

More posts like this