This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Exporting fasta from SQL

Hi everyone,

I have sequences stored in an SQL database, with the header stored in one field and the sequence (single line) in another.

header1 GACTACGACT...

header2 ATACGATCAT...

I've been exporting sequences as a csv and running another process to put them in proper fasta format. Can anyone suggest an SQL query that would pull them out in fasta format directly, also adding the ">" to the header? Some students are also using the data and they are still learning the basics of unix commands.

Any advice will be appreciated.

Stacy

fasta format sql
• 3,526 views • link
written by stacy734 • stacy734

1 answer

say you're using sqlite3:

$ sqlite3 db.sqlite3
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table fasta(name,sequence);
sqlite> insert into fasta(name,sequence) values ("abcd","ATGACAGATCGATCGATGTACGTAGCTGCTAGTGATATGCTAGCTGCTGATAGCTAGTAGCTAGC");
sqlite> insert into fasta(name,sequence) values ("efegh","ATGATCGTATGGGATCGAGTCTAAAAAAA");
sqlite> (ctrl-d)

export ('||' is the concatenation operator and x'0A' is the carriage return). Another sql engine would have a different syntax(mysql: 'concat') but the idea is the same.

$ sqlite3 db.sqlite3 "select ('>' || name || x'0A' || sequence) from fasta;"
>abcd
ATGACAGATCGATCGATGTACGTAGCTGCTAGTGATATGCTAGCTGCTGATAGCTAGTAGCTAGC
>efegh
ATGATCGTATGGGATCGAGTCTAAAAAAA
• 0 views • link
written by Pierre Lindenbaum Pierre Lindenbaum

Log in to answer this question.

More posts like this