Exporting fasta from SQL

Question

[stacy734]

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

[Pierre Lindenbaum]

what is your SQL engine ?

[stacy734]

Thank you very much!

Answer 1

[Pierre Lindenbaum]

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