aboutsummaryrefslogtreecommitdiff
path: root/wqflask/maintenance/gen_select_dataset.py
diff options
context:
space:
mode:
Diffstat (limited to 'wqflask/maintenance/gen_select_dataset.py')
-rw-r--r--wqflask/maintenance/gen_select_dataset.py94
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()