3
‹FQa|  ã               @   s@   d dl mZ d dlZdd„ Zdd„ Zdd„ Zd	d
„ Zdd„ ZdS )é    )Ú
sqlConnectNc          	   C   sæ   t jƒ }dj| ƒ}|j|ƒ}djdƒ}|j|ƒ}djdƒ}|j|ƒ}| |d t|d ƒdjt|d ƒƒdjt|d	 ƒƒdjt|d
 ƒƒdœ}|d k	r®d|d< tj|d ƒ|d< |d k	r¾d|d< djdƒ}	|j|	ƒ}
|
d k	râd|d< |S )NzÝSELECT name, COUNT(DISTINCT BodySite) site_num, COUNT(DISTINCT disease) disease_num, SUM(loaded_uid_num) total_runs, SUM(loaded_uid_num_QC1) valid_runs FROM mbodymap_species_to_samples WHERE ncbi_taxon_id={} GROUP BY namez<select attributes from cross_db_links where ncbi_taxon_id={}Úncbi_taxon_idz3select * from gmrepo_species where ncbi_taxon_id={}ÚnameÚsite_numz{:,}Ú
total_runsÚ
valid_runsÚdisease_num)r   r   r   r   r   r   TÚifMvpÚ
attributesÚmvpDataÚifGmrepoz0select * from hmdad_taxon where ncbi_taxon_id={}ÚifHmdad)r   ÚMySQLConnectionÚformatÚqueryOneÚintÚjsonÚloads)r   ÚconnectÚ	sql_introÚintroÚsql_mvpÚmvpÚ
sql_gmrepoÚgmrepoÚresultÚ	sql_hmdadÚhmdad© r   ú=/var/www/html/mybodymap/server/service/taxonsToSiteService.pyÚgetSpeciesIntro   s0    







r    c             C   s$   t jƒ }dj| ||ƒ}|j|ƒ}|S )Nz_SELECT * FROM mbodymap_species_to_site_relative_abundance WHERE ncbi_taxon_id={} ORDER BY {} {})r   r   r   Úquery)r   ÚpropNameÚ	propOrderr   Úsqlr   r   r   r   ÚgetSpeciesData"   s
    

r%   c          	   C   sæ   t jƒ }dj| ƒ}|j|ƒ}djdƒ}|j|ƒ}djdƒ}|j|ƒ}| |d t|d ƒdjt|d ƒƒdjt|d	 ƒƒdjt|d
 ƒƒdœ}|d k	r®d|d< tj|d ƒ|d< |d k	r¾d|d< djdƒ}	|j|	ƒ}
|
d k	râd|d< |S )NzÛSELECT name, COUNT(DISTINCT BodySite) site_num, COUNT(DISTINCT disease) disease_num, SUM(loaded_uid_num) total_runs, SUM(loaded_uid_num_QC1) valid_runs FROM mbodymap_genus_to_samples WHERE ncbi_taxon_id={} GROUP BY namez<select attributes from cross_db_links where ncbi_taxon_id={}r   z1select * from gmrepo_genus where ncbi_taxon_id={}r   r   z{:,}r   r   r   )r   r   r   r   r   r   Tr	   r
   r   r   z0select * from hmdad_taxon where ncbi_taxon_id={}r   )r   r   r   r   r   r   r   )r   r   r   r   r   r   r   r   r   r   r   r   r   r   ÚgetGenusIntro*   s0    







r&   c             C   s$   t jƒ }dj| ||ƒ}|j|ƒ}|S )Nz]SELECT * FROM mbodymap_genus_to_site_relative_abundance WHERE ncbi_taxon_id={} ORDER BY {} {})r   r   r   r!   )r   r"   r#   r   r$   r   r   r   r   ÚgetGenusDataH   s
    

r'   c          	   C   s  t jƒ }dj| ƒ}|j|ƒ}|d kr,d}|S dj| ƒ}|j|ƒ}dj| ƒ}|j|ƒ}g }	dj| ƒ}
|j|
ƒ}x–|D ]Ž}dj| |d |d ƒ}|j|ƒ}g }g }x8|D ]0}|j|d	 ƒ |j|d
 d |d  d ƒ q¤W |	j|d |d |d |d ||dœƒ qrW |||	dœ}|S d S )NzKselect * from curated_lefse_analysis_results where ncbi_taxon_id={} limit 1FzÈselect count(1) comparisons, count(distinct project_id) projects, count(distinct bodysite) bodysites, count(distinct phenotype2) unique_pheno from curated_lefse_analysis_results where ncbi_taxon_id={}zWSELECT * FROM `curated_lefse_analysis_results` WHERE ncbi_taxon_id={} ORDER BY lda DESCzƒselect distinct phenotype1, phenotype1_name, phenotype2, phenotype2_name from curated_lefse_analysis_results WHERE ncbi_taxon_id={}zƒselect project_id, bodysite, lda from curated_lefse_analysis_results WHERE ncbi_taxon_id={} and phenotype1="{}" and phenotype2="{}"Ú
phenotype1Ú
phenotype2ZldaÚ
project_idz (Úbodysiteú)Úphenotype1_nameÚphenotype2_name)r(   r)   r-   r.   ÚxaxisÚyaxis)r   ÚtableZbar)r   r   r   r   r!   Úappend)r   r   Ú
sql_markerÚ	if_markerÚmarkerr   r   Z	sql_tabler1   ZbarDataZ	sql_phenoÚ
phenotypesZphenoZsql_phenoDataZ	phenoDatar/   r0   Úitemr   r   r   r   ÚgetMarkerSpeciesP   sJ    






"r8   )Úutilsr   r   r    r%   r&   r'   r8   r   r   r   r   Ú<module>   s   