from service.taxonsToSiteService import getSpeciesData, getGenusData from utils import sqlConnect import json def getSpecies(checkedSites, page, limit, propName, propOrder, secondName, secondOrder): connect = sqlConnect.MySQLConnection() search = '' species_result = [] for item in checkedSites: if item == 'Large intestine': search += ("'Rectum'" + ',' + "'Colon'" + ',' + "'Cecum'" + ',') elif item == 'Small intestine': search += ("'Ileum'" + ',' + "'Duodenum'" + ',') else: search += ("'%s'" + ',') % (item) # print(search) search = search.rstrip(',') # sql_species = 'SELECT DISTINCT ncbi_taxon_id, name, site_num, SUM(loaded_uid_num) sample_num FROM mbodymap_species_to_site WHERE BodySite IN ({}) AND loaded_uid_num>1 GROUP BY ncbi_taxon_id, name, site_num ORDER BY {} {}'.format( # search, propName, propOrder) # print(sql_species) # species = connect.query(sql_species) sql_species_total = 'SELECT DISTINCT ncbi_taxon_id, name, site_num, SUM(loaded_uid_num) sample_num FROM mbodymap_species_to_site WHERE BodySite IN ({}) GROUP BY ncbi_taxon_id, name, site_num ORDER BY {} {}'.format( search, propName, propOrder) species_total = connect.query(sql_species_total) j = 0 for k in range(len(species_total) - 1, -1, -1): if j <= 500: if int(species_total[k]['sample_num']) == 1: species_total.remove(species_total[k]) j += 1 total = len(species_total) if page == 1: i = 0 for item in species_total: if i <= 9: # ----------- 判断是否是marker ------------------------------ sql_marker = 'select * from curated_lefse_analysis_results where ncbi_taxon_id={} limit 1'.format( item['ncbi_taxon_id']) if_marker = connect.queryOne(sql_marker) if if_marker is None: marker = False else: marker = True species_result.append({ 'name': item['name'], 'ncbi_taxon_id': item['ncbi_taxon_id'], 'site_num': item['site_num'], 'sample_num': int(item['sample_num']), 'marker': marker }) i += 1 else: i = 0 for item in species_total: if i <= (page - 1) * limit - 1: i += 1 continue if (page - 1) * limit - 1 < i <= page * limit - 1: # ----------- 判断是否是marker ------------------------------ sql_marker = 'select * from curated_lefse_analysis_results where ncbi_taxon_id={} limit 1'.format( item['ncbi_taxon_id']) if_marker = connect.queryOne(sql_marker) if if_marker is None: marker = False else: marker = True species_result.append({ 'name': item['name'], 'ncbi_taxon_id': item['ncbi_taxon_id'], 'site_num': item['site_num'], 'sample_num': int(item['sample_num']), 'marker': marker }) i += 1 if i > page * limit - 1: break result = getTaxonsPageSpeciseData(species_result, secondName, secondOrder) return result, total def getTaxonsPageSpeciseData(arr, secondName, secondOrder): connect = sqlConnect.MySQLConnection() for item in arr: sql = 'SELECT * FROM mbodymap_species_to_site_relative_abundance WHERE ncbi_taxon_id={} ORDER BY {} {}'.format( item['ncbi_taxon_id'], secondName, secondOrder) result = connect.query(sql) item['secondTableData'] = result sql_mvp = 'select attributes from cross_db_links where ncbi_taxon_id={}'.format(item['ncbi_taxon_id']) mvp = connect.queryOne(sql_mvp) if mvp is not None: item['ifMvp'] = True item['mvpData'] = json.loads(mvp['attributes']) # print(type(mvp['attributes'])) sql_gmrepo = 'select * from gmrepo_species where ncbi_taxon_id={}'.format(item['ncbi_taxon_id']) gmrepo = connect.queryOne(sql_gmrepo) if gmrepo is not None: item['ifGmrepo'] = True sql_hmdad = 'select * from hmdad_taxon where ncbi_taxon_id={}'.format(item['ncbi_taxon_id']) hmdad = connect.queryOne(sql_hmdad) if hmdad is not None: item['ifHmdad'] = True return arr def getTaxonsPageGenusData(arr, secondName, secondOrder): connect = sqlConnect.MySQLConnection() for item in arr: sql = 'SELECT * FROM mbodymap_genus_to_site_relative_abundance WHERE ncbi_taxon_id={} ORDER BY {} {}'.format( item['ncbi_taxon_id'], secondName, secondOrder) # print(sql) result = connect.query(sql) item['secondTableData'] = result sql_mvp = 'select attributes from cross_db_links where ncbi_taxon_id={}'.format(item['ncbi_taxon_id']) mvp = connect.queryOne(sql_mvp) if mvp is not None: item['ifMvp'] = True item['mvpData'] = json.loads(mvp['attributes']) # print(type(mvp['attributes'])) sql_gmrepo = 'select * from gmrepo_genus where ncbi_taxon_id={}'.format(item['ncbi_taxon_id']) gmrepo = connect.queryOne(sql_gmrepo) if gmrepo is not None: item['ifGmrepo'] = True sql_hmdad = 'select * from hmdad_taxon where ncbi_taxon_id={}'.format(item['ncbi_taxon_id']) hmdad = connect.queryOne(sql_hmdad) if hmdad is not None: item['ifHmdad'] = True return arr def getSpeciesIntro(): connect = sqlConnect.MySQLConnection() sql_sample_num = 'SELECT SUM(loaded_run_num) sample_num FROM mbodymap_intro' sample_num = int(connect.queryOne(sql_sample_num)['sample_num']) sql_species_num = 'SELECT COUNT(DISTINCT ncbi_taxon_id) species_num FROM mbodymap_species_to_site' species_num = connect.queryOne(sql_species_num)['species_num'] sql_genus_num = 'SELECT COUNT(DISTINCT ncbi_taxon_id) genus_num FROM mbodymap_genus_to_site' genus_num = connect.queryOne(sql_genus_num)['genus_num'] sql_filter_species_num = 'SELECT COUNT(DISTINCT ncbi_taxon_id) species_num FROM mbodymap_species_to_site WHERE loaded_uid_num>1 AND med>=0.01' filter_species_num = connect.queryOne(sql_filter_species_num)['species_num'] sql_filter_genus_num = 'SELECT COUNT(DISTINCT ncbi_taxon_id) genus_num FROM mbodymap_genus_to_site WHERE loaded_uid_num>1 AND med>=0.01' filter_genus_num = connect.queryOne(sql_filter_genus_num)['genus_num'] species_intro = { 'sample_num': sample_num, 'species_num': species_num, 'genus_num': genus_num, 'filter_species_num': filter_species_num, 'filter_genus_num': filter_genus_num } return species_intro def getSpeciesPhenotype(ncbi_taxon_id, site): connect = sqlConnect.MySQLConnection() result = [] sql_phenotype = 'SELECT disease, loaded_uid_num_QC1, relative_abundance_std, relative_abundance_avg, relative_abundance_med FROM mbodymap_species_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}"'.format( ncbi_taxon_id, site) phenotypes = connect.query(sql_phenotype) for phenotype in phenotypes: sql_total_disease_of_taxon_in_site = 'SELECT COUNT(1) total FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE disease="{}" AND BodySite="{}" AND t1.run_id=t2.accession_id AND t2.QCStatus=1'.format( phenotype['disease'], site) total_disease_of_taxon_in_site = connect.queryOne(sql_total_disease_of_taxon_in_site)['total'] sql_disease_name = 'SELECT term FROM mesh_data WHERE uid="{}"'.format(phenotype['disease']) name = connect.queryOne(sql_disease_name)['term'] result.append({ 'phenotype': name, 'mesh_id': phenotype['disease'], 'loaded_uid_num': phenotype['loaded_uid_num_QC1'], 'std': phenotype['relative_abundance_std'], 'avg': phenotype['relative_abundance_avg'], 'med': phenotype['relative_abundance_med'], 'total': int(total_disease_of_taxon_in_site) }) return result def getGenus(checkedSites, page, limit, propName, propOrder, secondName, secondOrder): connect = sqlConnect.MySQLConnection() search = '' species_result = [] for item in checkedSites: if item == 'Large intestine': search += ("'Rectum'" + ',' + "'Colon'" + ',' + "'Cecum'" + ',') elif item == 'Small intestine': search += ("'Ileum'" + ',' + "'Duodenum'" + ',') else: search += ("'%s'" + ',') % (item) search = search.rstrip(',') # sql_species = 'SELECT DISTINCT ncbi_taxon_id, name, site_num, SUM(loaded_uid_num) sample_num FROM mbodymap_genus_to_site WHERE BodySite IN ({}) AND loaded_uid_num>1 GROUP BY ncbi_taxon_id, name, site_num ORDER BY {} {}'.format( # search, propName, propOrder) # species = connect.query(sql_species) # total = len(species) sql_species_total = 'SELECT DISTINCT ncbi_taxon_id, name, site_num, SUM(loaded_uid_num) sample_num FROM mbodymap_genus_to_site WHERE BodySite IN ({}) GROUP BY ncbi_taxon_id, name, site_num ORDER BY {} {}'.format( search, propName, propOrder) species_total = connect.query(sql_species_total) j = 0 for k in range(len(species_total) - 1, -1, -1): if j <= 50: if int(species_total[k]['sample_num']) == 1: species_total.remove(species_total[k]) j += 1 total = len(species_total) if page == 1: i = 0 for item in species_total: if i <= 9: # ----------- 判断是否是marker ------------------------------ sql_marker = 'select * from curated_lefse_analysis_results where ncbi_taxon_id={} limit 1'.format( item['ncbi_taxon_id']) if_marker = connect.queryOne(sql_marker) if if_marker is None: marker = False else: marker = True species_result.append({ 'name': item['name'], 'ncbi_taxon_id': item['ncbi_taxon_id'], 'site_num': item['site_num'], 'sample_num': int(item['sample_num']), 'marker': marker }) i += 1 else: i = 0 for item in species_total: if i <= (page - 1) * limit - 1: i += 1 continue if (page - 1) * limit - 1 < i <= page * limit - 1: # ----------- 判断是否是marker ------------------------------ sql_marker = 'select * from curated_lefse_analysis_results where ncbi_taxon_id={} limit 1'.format( item['ncbi_taxon_id']) if_marker = connect.queryOne(sql_marker) if if_marker is None: marker = False else: marker = True species_result.append({ 'name': item['name'], 'ncbi_taxon_id': item['ncbi_taxon_id'], 'site_num': item['site_num'], 'sample_num': int(item['sample_num']), 'marker': marker }) i += 1 if i > page * limit - 1: break result = getTaxonsPageGenusData(species_result, secondName, secondOrder) return result, total def getGenusPhenotype(ncbi_taxon_id, site): connect = sqlConnect.MySQLConnection() result = [] sql_phenotype = 'SELECT disease, loaded_uid_num_QC1, relative_abundance_std, relative_abundance_avg, relative_abundance_med FROM mbodymap_genus_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}"'.format( ncbi_taxon_id, site) # print(sql_phenotype) phenotypes = connect.query(sql_phenotype) for phenotype in phenotypes: sql_total_disease_of_taxon_in_site = 'SELECT COUNT(1) total FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE disease="{}" AND BodySite="{}" AND t1.run_id=t2.accession_id AND t2.QCStatus=1'.format( phenotype['disease'], site) total_disease_of_taxon_in_site = connect.queryOne(sql_total_disease_of_taxon_in_site)['total'] sql_disease_name = 'SELECT term FROM mesh_data WHERE uid="{}"'.format(phenotype['disease']) name = connect.queryOne(sql_disease_name)['term'] result.append({ 'phenotype': name, 'mesh_id': phenotype['disease'], 'loaded_uid_num': phenotype['loaded_uid_num_QC1'], 'std': phenotype['relative_abundance_std'], 'avg': phenotype['relative_abundance_avg'], 'med': phenotype['relative_abundance_med'], 'total': int(total_disease_of_taxon_in_site) }) return result