[ensembl-dev] MySQL query to retrieve protein annotation

Fri Feb 24 14:13:06 GMT 2017

Ah, I see older Ensembl builds have NULL for all protein_feature.hit_description (in the case of PFAM at least). Such as homo_sapiens_core_75_37 and mus_musculus_core_75_38, where as builds like mus_musculus_core_84_38 to have non-NULL entries. What is the reason?

On Feb 24, 2017, at 08:53, Charles Joseph Murphy <chm2059 at med.cornell.edu<mailto:chm2059 at med.cornell.edu>> wrote:

Ah, I see! Got what I need then.

I did look at that before, but was looking at a protein that was NULL for for all of its protein_feature.hit_description—so I thought that must not be the column I wanted. Indeed, when I looked at another protein I see the relevant protein feature descriptions.

Thanks,
Charlie

On Feb 24, 2017, at 08:47, mag <mr6 at ebi.ac.uk<mailto:mr6 at ebi.ac.uk>> wrote:

Hi Charles,

Could you please include a link to the display you are looking at?

The data you are retrieving from the MySQL database is displayed on the 'Domains and Features' tab, for example
http://www.ensembl.org/Homo_sapiens/Transcript/Domains?db=core;g=ENSG00000167657;r=19:3959639-3961159;t=ENST00000594894<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.ensembl.org_Homo-5Fsapiens_Transcript_Domains-3Fdb-3Dcore-3Bg-3DENSG00000167657-3Br-3D19-3A3959639-2D3961159-3Bt-3DENST00000594894&d=DwMD-g&c=lb62iw4YL4RFalcE2hQUQealT9-RXrryqt9KZX2qu2s&r=O3yXKBF_L8Fov58BXORGXKqPP85pYddrOwCg4PV2BCY&m=42JQQMixyqEk_50qjTplRgTZ_EGDJJsuQveAoAiYHF4&s=pqq2PH9CrrIMGHwMPH48uwxJ7PkIc0Z7zat21aZAxn0&e=>
This displays a description like 'Protein kinase domain', which is the protein_feature.hit_description field from the SQL query.

Hope that helps,
mag

On 24/02/2017 13:39, Charles Joseph Murphy wrote:
Thanks for your suggestion.

Sorry to bother with another question, but I’ve spent a couple hours trying to figure this out and wonder if either of you could help me with something else. How might I go about getting the descriptions of the protein annotations. For example, for PFAM domains I want what is displayed on the Ensembl website. E.g. “Pkinase” instead of PF00069.

Thanks!

On Feb 23, 2017, at 04:30, Michal Dabrowski <m.dabrowski at nencki.gov.pl<mailto:m.dabrowski at nencki.gov.pl>> wrote:

How about removing the table "translation" in the FROM part of your query?
Or adding sth like " AND translation.translation_id='291089’ " to the WHERE part.
Now it seems you will get all the entries from the "translation" table.
m.

Michal Dabrowski
Laboratory of Bioinformatics
Nencki Institute
Pasteur 3 Str
02-093 Warsaw, Poland
phone: 48 22 58 92 575

On Feb 22, 2017, at 5:43 PM, Charles Joseph Murphy wrote:

Hi,

I’ am trying to use the Ensembl public MySQL query to retrieve protein annotation for all of a genome’s genes. While trying to figure out the right MySQL queries to make, I’ am testing it out with just one transcript’s translation (ENSMUST00000120187):

mysql> use mus_musculus_core_75_38;
mysql> SELECT protein_feature.hit_name, protein_feature.hit_start, protein_feature.hit_end, protein_feature.seq_start, protein_feature.seq_end, protein_feature.evalue, protein_feature.perc_ident, protein_feature.hit_description, protein_feature.translation_id FROM protein_feature, translation WHERE protein_feature.translation_id = '291089’;

But this returns around 1.4 million entries. What might be the best way to get all the protein annotation for ENSMUST00000120187? I’ am aware of BioMart and the Perl API, but would rather query via MySQL. I’ am continuing to read into the InterProScan pipeline and Ensembl documentation, but am not quite sure yet how to go about this.

Thanks for any help!
Charlie
_______________________________________________
Dev mailing list    Dev at ensembl.org<mailto:Dev at ensembl.org>
Posting guidelines and subscribe/unsubscribe info: http://lists.ensembl.org/mailman/listinfo/dev<https://urldefense.proofpoint.com/v2/url?u=http-3A__lists.ensembl.org_mailman_listinfo_dev&d=DwMF-g&c=lb62iw4YL4RFalcE2hQUQealT9-RXrryqt9KZX2qu2s&r=O3yXKBF_L8Fov58BXORGXKqPP85pYddrOwCg4PV2BCY&m=r6AnPQHwIIxjPPMd57hredgdCfvDhAjn5pgueJbAAnw&s=H9KJ4D9X5HDFPMb_Zi-YBdhwedbaUpfuIqz1qSDpqh0&e=>
Ensembl Blog: http://www.ensembl.info/<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.ensembl.info_&d=DwMF-g&c=lb62iw4YL4RFalcE2hQUQealT9-RXrryqt9KZX2qu2s&r=O3yXKBF_L8Fov58BXORGXKqPP85pYddrOwCg4PV2BCY&m=r6AnPQHwIIxjPPMd57hredgdCfvDhAjn5pgueJbAAnw&s=81t5xwFAfijclq_J5KpIknzBPxqqbJvgKSHMxehclZs&e=>

_______________________________________________
Dev mailing list    Dev at ensembl.org<mailto:Dev at ensembl.org>
Posting guidelines and subscribe/unsubscribe info: http://lists.ensembl.org/mailman/listinfo/dev<https://urldefense.proofpoint.com/v2/url?u=http-3A__lists.ensembl.org_mailman_listinfo_dev&d=DwMD-g&c=lb62iw4YL4RFalcE2hQUQealT9-RXrryqt9KZX2qu2s&r=O3yXKBF_L8Fov58BXORGXKqPP85pYddrOwCg4PV2BCY&m=42JQQMixyqEk_50qjTplRgTZ_EGDJJsuQveAoAiYHF4&s=6eMK3cANHy5p-FkuTgnZ39tDHFQZdsENna4T9m1dwa4&e=>
Ensembl Blog: http://www.ensembl.info/<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.ensembl.info_&d=DwMD-g&c=lb62iw4YL4RFalcE2hQUQealT9-RXrryqt9KZX2qu2s&r=O3yXKBF_L8Fov58BXORGXKqPP85pYddrOwCg4PV2BCY&m=42JQQMixyqEk_50qjTplRgTZ_EGDJJsuQveAoAiYHF4&s=IzM8e5UuzvqVqJoD7ky2kVEcoFTnpiSOHm-1yinpUVI&e=>

_______________________________________________
Dev mailing list    Dev at ensembl.org<mailto:Dev at ensembl.org>
Posting guidelines and subscribe/unsubscribe info: https://urldefense.proofpoint.com/v2/url?u=http-3A__lists.ensembl.org_mailman_listinfo_dev&d=DwICAg&c=lb62iw4YL4RFalcE2hQUQealT9-RXrryqt9KZX2qu2s&r=O3yXKBF_L8Fov58BXORGXKqPP85pYddrOwCg4PV2BCY&m=42JQQMixyqEk_50qjTplRgTZ_EGDJJsuQveAoAiYHF4&s=6eMK3cANHy5p-FkuTgnZ39tDHFQZdsENna4T9m1dwa4&e=
Ensembl Blog: https://urldefense.proofpoint.com/v2/url?u=http-3A__www.ensembl.info_&d=DwICAg&c=lb62iw4YL4RFalcE2hQUQealT9-RXrryqt9KZX2qu2s&r=O3yXKBF_L8Fov58BXORGXKqPP85pYddrOwCg4PV2BCY&m=42JQQMixyqEk_50qjTplRgTZ_EGDJJsuQveAoAiYHF4&s=IzM8e5UuzvqVqJoD7ky2kVEcoFTnpiSOHm-1yinpUVI&e=

_______________________________________________
Dev mailing list    Dev at ensembl.org<mailto:Dev at ensembl.org>
Posting guidelines and subscribe/unsubscribe info: https://urldefense.proofpoint.com/v2/url?u=http-3A__lists.ensembl.org_mailman_listinfo_dev&d=DwICAg&c=lb62iw4YL4RFalcE2hQUQealT9-RXrryqt9KZX2qu2s&r=O3yXKBF_L8Fov58BXORGXKqPP85pYddrOwCg4PV2BCY&m=sFzq4UGStyyyqxcYwKg1sYrx82JQgUeOKkdbO6WDTkk&s=tWQF7a5jljCmCymixRJ2FP_GLdrOMXx54efVJpRPlNA&e=
Ensembl Blog: https://urldefense.proofpoint.com/v2/url?u=http-3A__www.ensembl.info_&d=DwICAg&c=lb62iw4YL4RFalcE2hQUQealT9-RXrryqt9KZX2qu2s&r=O3yXKBF_L8Fov58BXORGXKqPP85pYddrOwCg4PV2BCY&m=sFzq4UGStyyyqxcYwKg1sYrx82JQgUeOKkdbO6WDTkk&s=JMBBIONVy389Y7sX5ni0e0yfHEyGM63l7RTeC83AAsI&e=

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ensembl.org/pipermail/dev_ensembl.org/attachments/20170224/be16a83c/attachment.html>