diff options
Diffstat (limited to 'wqflask/maintenance/gen_select_dataset.py')
| -rw-r--r-- | wqflask/maintenance/gen_select_dataset.py | 94 |
1 files changed, 74 insertions, 20 deletions
diff --git a/wqflask/maintenance/gen_select_dataset.py b/wqflask/maintenance/gen_select_dataset.py index 4c544192..ad560659 100644 --- a/wqflask/maintenance/gen_select_dataset.py +++ b/wqflask/maintenance/gen_select_dataset.py @@ -29,25 +29,47 @@ It needs to be run manually when database has been changed. from __future__ import print_function, division -import sys +#from flask import config +# +#cdict = {} +#config = config.Config(cdict).from_envvar('WQFLASK_SETTINGS') +#print("cdict is:", cdict) -sys.path.insert(0, "..") +import our_settings import MySQLdb import simplejson as json +import urlparse -from pprint import pformat as pf -from base import webqtlConfig +#import sqlalchemy as sa + +from pprint import pformat as pf +#Engine = sa.create_engine(our_settings.SQLALCHEMY_DATABASE_URI) # build MySql database connection -Con = MySQLdb.Connect(db=webqtlConfig.DB_NAME, - host=webqtlConfig.MYSQL_SERVER, - user=webqtlConfig.DB_USER, - passwd=webqtlConfig.DB_PASSWD) -Cursor = Con.cursor() + +#conn = Engine.connect() + + + + + +def parse_db_uri(db_uri): + """Converts a database URI to the db name, host name, user name, and password""" + + parsed_uri = urlparse.urlparse(our_settings.DB_URI) + + db_conn_info = dict( + db = parsed_uri.path[1:], + host = parsed_uri.hostname, + user = parsed_uri.username, + passwd = parsed_uri.password) + + return db_conn_info + def get_species(): @@ -77,12 +99,14 @@ def get_groups(species): def get_types(groups): """Build types list""" types = {} + print("Groups: ", pf(groups)) for species, group_dict in groups.iteritems(): types[species] = {} for group_name, _group_full_name in group_dict: # make group an alias to shorten the code types[species][group_name] = [("Phenotypes", "Phenotypes"), ("Genotypes", "Genotypes")] types[species][group_name] += build_types(species, group_name) + return types @@ -93,17 +117,32 @@ def build_types(species, group): (all types except phenotype/genotype are tissues) """ - Cursor.execute("""select distinct Tissue.Name, concat(Tissue.Name, ' mRNA') + + print("""select distinct Tissue.Name + from ProbeFreeze, ProbeSetFreeze, InbredSet, Tissue, Species + where Species.Name = '{}' and Species.Id = InbredSet.SpeciesId and + InbredSet.Name = '{}' and + ProbeFreeze.TissueId = Tissue.Id and + ProbeFreeze.InbredSetId = InbredSet.Id and + ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id and + ProbeSetFreeze.public > 0 + order by Tissue.Name""".format(species, group)) + Cursor.execute("""select distinct Tissue.Name from ProbeFreeze, ProbeSetFreeze, InbredSet, Tissue, Species where Species.Name = %s and Species.Id = InbredSet.SpeciesId and InbredSet.Name = %s and ProbeFreeze.TissueId = Tissue.Id and ProbeFreeze.InbredSetId = InbredSet.Id and ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id and - ProbeSetFreeze.public > %s - order by Tissue.Name""", (species, group, webqtlConfig.PUBLICTHRESH)) - return Cursor.fetchall() - + ProbeSetFreeze.public > 0 + order by Tissue.Name""", (species, group)) + + results = [] + for result in Cursor.fetchall(): + if len(result): + results.append((result[0], result[0])) + + return results def get_datasets(types): """Build datasets list""" @@ -111,9 +150,10 @@ def get_datasets(types): for species, group_dict in types.iteritems(): datasets[species] = {} for group, type_list in group_dict.iteritems(): + print("type_list: ", type_list) datasets[species][group] = {} - for type_name, _type_full_name in type_list: - datasets[species][group][type_name] = build_datasets(species, group, type_name) + for type_name in type_list: + datasets[species][group][type_name[0]] = build_datasets(species, group, type_name[0]) return datasets @@ -134,20 +174,30 @@ def build_datasets(species, group, type_name): if dataset_value: return [(dataset_value, dataset_text)] else: + print("""select ProbeSetFreeze.Name, ProbeSetFreeze.FullName from + ProbeSetFreeze, ProbeFreeze, InbredSet, Tissue, Species where + Species.Name = '{}' and Species.Id = InbredSet.SpeciesId and + InbredSet.Name = '{}' and + ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id and Tissue.Name = '{}' + and ProbeFreeze.TissueId = Tissue.Id and ProbeFreeze.InbredSetId = + InbredSet.Id and ProbeSetFreeze.public > 0 order by + ProbeSetFreeze.CreateTime desc""".format(species, group, type_name)) Cursor.execute("""select ProbeSetFreeze.Name, ProbeSetFreeze.FullName from ProbeSetFreeze, ProbeFreeze, InbredSet, Tissue, Species where Species.Name = %s and Species.Id = InbredSet.SpeciesId and InbredSet.Name = %s and ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id and Tissue.Name = %s and ProbeFreeze.TissueId = Tissue.Id and ProbeFreeze.InbredSetId = - InbredSet.Id and ProbeSetFreeze.public > %s order by - ProbeSetFreeze.CreateTime desc""", ( - species, group, type_name, webqtlConfig.PUBLICTHRESH)) + InbredSet.Id and ProbeSetFreeze.public > 0 order by + ProbeSetFreeze.CreateTime desc""", (species, group, type_name)) return Cursor.fetchall() def main(): """Generates and outputs (as json file) the data for the main dropdown menus on the home page""" + + parse_db_uri(our_settings.DB_URI) + species = get_species() groups = get_groups(species) types = get_types(groups) @@ -167,6 +217,8 @@ def main(): datasets=datasets, ) + print("data:", data) + output_file = """../wqflask/static/new/javascript/dataset_menu_structure.json""" with open(output_file, 'w') as fh: @@ -182,5 +234,7 @@ def _test_it(): datasets = build_datasets("Mouse", "BXD", "Hippocampus") print("build_datasets:", pf(datasets)) -if __name__ == '__main__': +if __name__ == '__main__': + Conn = MySQLdb.Connect(**parse_db_uri(our_settings.DB_URI)) + Cursor = Conn.cursor() main() |