<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;}
span.EmailStyle21
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal">Thanks Marc, <o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I’m so glad to have this pointed out to me, as well to find a handy graphical schema embedded in the README for
<a href="https://github.com/Ensembl/ensembl-metadata">https://github.com/Ensembl/ensembl-metadata</a><o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Duly armed, I can approximately reproduce the content of <a href="https://ftp.ensembl.org/pub/release-104/species_EnsemblVertebrates.txt">
https://ftp.ensembl.org/pub/release-104/species_EnsemblVertebrates.txt</a> as:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">mysql --host=ensembldb.ensembl.org --port=3306 --user=anonymous -A -B -e '<o:p></o:p></p>
<p class="MsoNormal">select<o:p></o:p></p>
<p class="MsoNormal">display_name, scientific_name, strain,<o:p></o:p></p>
<p class="MsoNormal">d.name, o.taxonomy_id,<o:p></o:p></p>
<p class="MsoNormal">a.assembly_accession, a.assembly_name, a.assembly_default, a.assembly_ucsc,<o:p></o:p></p>
<p class="MsoNormal">g.genebuild,<o:p></o:p></p>
<p class="MsoNormal">dr.ensembl_version, dr.ensembl_genomes_version,<o:p></o:p></p>
<p class="MsoNormal">gdb.type, gdb.dbname, reference<o:p></o:p></p>
<p class="MsoNormal">from genome_annotation ga<o:p></o:p></p>
<p class="MsoNormal">join genome_database gdb using (genome_database_id)<o:p></o:p></p>
<p class="MsoNormal">join genome g on (gdb.genome_id = g.genome_id)<o:p></o:p></p>
<p class="MsoNormal">join assembly a using (assembly_id)<o:p></o:p></p>
<p class="MsoNormal">join division d on (g.division_id=d.division_id)<o:p></o:p></p>
<p class="MsoNormal">join data_release dr on (g.data_release_id=dr.data_release_id)<o:p></o:p></p>
<p class="MsoNormal">join data_release_database drdb on (dr.data_release_id=drdb.data_release_id and d.division_id = drdb.division_id)<o:p></o:p></p>
<p class="MsoNormal">join organism o using (organism_id)<o:p></o:p></p>
<p class="MsoNormal">where<o:p></o:p></p>
<p class="MsoNormal">ga.type = "genebuild_method"<o:p></o:p></p>
<p class="MsoNormal">and gdb.type = "core"<o:p></o:p></p>
<p class="MsoNormal">and drdb.type != "mart"<o:p></o:p></p>
<p class="MsoNormal">and dr.is_current = 1<o:p></o:p></p>
<p class="MsoNormal">;’<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I’ve been down Ensembl’s Perl API rabbit hole a few times with before, and for my current application, prefer just to sling the SQL.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thanks again,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">~Malcolm Cook<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div style="border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt">
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b>From:</b> Dev <dev-bounces@ensembl.org> <b>On Behalf Of </b>
mchakiachvili<br>
<b>Sent:</b> Tuesday, June 8, 2021 10:49 AM<br>
<b>To:</b> Ensembl developers list <dev@ensembl.org><br>
<b>Subject:</b> Re: [ensembl-dev] is SQL access available for Ensembl Species List and Table of assemblies?<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<table class="MsoNormalTable" border="0" cellspacing="3" cellpadding="0">
<tbody>
<tr>
<td width="600" style="width:6.25in;padding:.75pt .75pt .75pt .75pt">
<div style="border:solid #CAD0D1 3.0pt;padding:0in 0in 0in 0in">
<p class="MsoNormal" align="center" style="text-align:center;line-height:12.0pt;background:#E45217">
<i><span style="color:#FCE815">ATTENTION:</span></i><span style="color:black"> </span>
<i><span style="color:#FCE815">This email came from an external source. Do not open attachments or click on links from unknown senders or unexpected emails.</span></i><span style="color:black">
<o:p></o:p></span></p>
</div>
</td>
</tr>
</tbody>
</table>
<p class="MsoNormal" style="margin-bottom:12.0pt"><o:p> </o:p></p>
<div>
<p class="MsoNormal">Hi Malcom, <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt">You'll need to access our metadata databases in order to retrieve these information, you can find the `ensembl_metadata_104` database on our public server:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">`mysql --host=ensembldb.ensembl.org --port=3306 --user=anonymous`<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Database hold a organism <-> genome (s) association per release, where you'll find your information. <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">To access programmatically I would invite you to use our dedicated PERL API available here <a href="https://github.com/Ensembl/ensembl-metadata">https://github.com/Ensembl/ensembl-metadata</a> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">If you only need to retrieve information for a particular release, please have a look at our set of useful script to retrieve data <a href="https://github.com/Ensembl/ensembl-metadata/tree/release/104/misc_scripts">https://github.com/Ensembl/ensembl-metadata/tree/release/104/misc_scripts</a><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">More info about our PERL software stack available here <a href="https://www.ensembl.org/info/docs/index.html">https://www.ensembl.org/info/docs/index.html</a><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Hope that will help you to sort things out. <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Marc<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">On Sun, 2021-06-06 at 20:47 +0000, Cook, Malcolm wrote:<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #729FCF 1.5pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt">
<div>
<p class="MsoNormal">Dear Ensembl Devs,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">I am looking for programmatic way to access in tabular form the data behind these two pages:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"> [Species List](<a href="https://www.ensembl.org/info/about/species.html">https://www.ensembl.org/info/about/species.html</a>)<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"> [Table of assemblies](<a href="https://www.ensembl.org/info/website/archives/assembly.html">https://www.ensembl.org/info/website/archives/assembly.html</a>)<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Preferably a SQL query on remotely accessible mysql server.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Additionally, if it were possible to include the name used by UCSC for the assembly, if available.... (perhaps this is the same as asking for NCBI's alternate name)<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Thanks for any tips and suggestions.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Malcolm Cook<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Database Applications Manager<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Stowers Institute for Medical Research<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Kansas City, MO USA I<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">_______________________________________________<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Dev mailing list <a href="mailto:Dev@ensembl.org">Dev@ensembl.org</a><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Posting guidelines and subscribe/unsubscribe info: <a href="https://lists.ensembl.org/mailman/listinfo/dev_ensembl.org">
https://lists.ensembl.org/mailman/listinfo/dev_ensembl.org</a><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Ensembl Blog: <a href="http://www.ensembl.info/">http://www.ensembl.info/</a><o:p></o:p></p>
</div>
</blockquote>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<pre>-- <o:p></o:p></pre>
<div>
<p class="MsoNormal">Marc Chakiachvili<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Ensembl Production Project Leader - Genomics Technology Infrastructure<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">European Bioinformatics Institute (EMBL-EBI)<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">European Molecular Biology Laboratory<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Wellcome Trust Genome Campus<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Hinxton<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Cambridge CB10 1SD<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">United Kingdom<o:p></o:p></p>
</div>
</div>
</div>
</div>
</body>
</html>