aboutsummaryrefslogtreecommitdiff
path: root/wqflask
diff options
context:
space:
mode:
Diffstat (limited to 'wqflask')
-rw-r--r--wqflask/maintenance/quick_search_table.py50
-rw-r--r--wqflask/wqflask/do_search.py41
2 files changed, 81 insertions, 10 deletions
diff --git a/wqflask/maintenance/quick_search_table.py b/wqflask/maintenance/quick_search_table.py
new file mode 100644
index 00000000..a337a543
--- /dev/null
+++ b/wqflask/maintenance/quick_search_table.py
@@ -0,0 +1,50 @@
+from __future__ import print_function, division, absolute_import
+
+import sys
+sys.path.append("../../..")
+
+import sqlalchemy as sa
+from sqlalchemy.dialects import mysql
+from sqlalchemy.orm import scoped_session, sessionmaker, relationship, backref
+from sqlalchemy.orm.exc import NoResultFound
+from sqlalchemy.ext.declarative import declarative_base
+
+import zach_settings as zs
+
+Engine = sa.create_engine(zs.SQLALCHEMY_DATABASE_URI,
+ #encoding='utf-8',
+ #client_encoding='utf-8',
+ echo="debug",
+ )
+
+Session = scoped_session(sessionmaker(bind=Engine)) #, extension=VersionedListener()))
+#Xsession = Session()
+
+Base = declarative_base(bind=Engine)
+Metadata = sa.MetaData()
+Metadata.bind = Engine
+
+
+class ProbeSet(Base):
+ __tablename__ = 'ProbeSet'
+ __table_args__ = {'autoload': True}
+
+def main():
+ for ps in page_query(Session.query(ProbeSet)): #all()
+ #for ps in probe_sets:
+ print("--->", ps)
+
+def page_query(q):
+ """http://stackoverflow.com/a/1217947/1175849"""
+ offset = 0
+ while True:
+ r = False
+ for elem in q.limit(1000).offset(offset):
+ r = True
+ yield elem
+ offset += 1000
+ if not r:
+ break
+
+if __name__ == "__main__":
+ main() \ No newline at end of file
diff --git a/wqflask/wqflask/do_search.py b/wqflask/wqflask/do_search.py
index 1b1b56fb..bbb16054 100644
--- a/wqflask/wqflask/do_search.py
+++ b/wqflask/wqflask/do_search.py
@@ -13,6 +13,7 @@ sys.path.append("..")
from dbFunction import webqtlDatabaseFunction
+from utility.benchmark import Bench
class DoSearch(object):
"""Parent class containing parameters/functions used for all searches"""
@@ -63,9 +64,9 @@ class DoSearch(object):
class QuickMrnaAssaySearch(DoSearch):
"""A general search for mRNA assays"""
-
+
DoSearch.search_types['quick_mrna_assay'] = "QuickMrnaAssaySearch"
-
+
base_query = """SELECT ProbeSet.Name as ProbeSet_Name,
ProbeSet.Symbol as ProbeSet_Symbol,
ProbeSet.description as ProbeSet_Description,
@@ -73,7 +74,7 @@ class QuickMrnaAssaySearch(DoSearch):
ProbeSet.Mb as ProbeSet_Mb,
ProbeSet.name_num as ProbeSet_name_num
FROM ProbeSet """
-
+
header_fields = ['',
'Record ID',
'Symbol',
@@ -190,6 +191,23 @@ class PhenotypeSearch(DoSearch):
'Max LRS',
'Max LRS Location']
+ #def get_fields_clause(self):
+ # """Generate clause for WHERE portion of query"""
+ #
+ # #Todo: Zach will figure out exactly what both these lines mean
+ # #and comment here
+ # if "'" not in self.search_term[0]:
+ # search_term = "[[:<:]]" + self.search_term[0] + "[[:>:]]"
+ #
+ # # This adds a clause to the query that matches the search term
+ # # against each field in the search_fields tuple
+ # fields_clause = []
+ # for field in self.search_fields:
+ # fields_clause.append('''%s REGEXP "%s"''' % (field, search_term))
+ # fields_clause = "(%s) and " % ' OR '.join(fields_clause)
+ #
+ # return fields_clause
+
def get_fields_clause(self):
"""Generate clause for WHERE portion of query"""
@@ -200,13 +218,13 @@ class PhenotypeSearch(DoSearch):
# This adds a clause to the query that matches the search term
# against each field in the search_fields tuple
- fields_clause = []
- for field in self.search_fields:
- fields_clause.append('''%s REGEXP "%s"''' % (field, search_term))
- fields_clause = "(%s) and " % ' OR '.join(fields_clause)
+ fields_clause = "MATCH("
+ fields_clause += ",".join(self.search_fields) + ") "
+ fields_clause += "AGAINST('{}' IN BOOLEAN MODE)".format(self.search_term[0])
return fields_clause
+
def compile_final_query(self, from_clause = '', where_clause = ''):
"""Generates the final query string"""
@@ -262,13 +280,13 @@ class QuickPhenotypeSearch(PhenotypeSearch):
'Publication.PubMed_ID',
'Publication.Abstract',
'Publication.Title',
- 'Publication.Authors')
+ 'Publication.Authors')
def compile_final_query(self, where_clause = ''):
"""Generates the final query string"""
query = (self.base_query +
- """WHERE %s
+ """WHERE (%s) and
PublishXRef.PhenotypeId = Phenotype.Id and
PublishXRef.PublicationId = Publication.Id and
PublishXRef.InbredSetId = InbredSet.Id and
@@ -283,7 +301,10 @@ class QuickPhenotypeSearch(PhenotypeSearch):
query = self.compile_final_query(where_clause = self.get_fields_clause())
- return self.execute(query)
+ with Bench("Doing quick phenotype search"):
+ results = self.execute(query)
+
+ return results
class GenotypeSearch(DoSearch):
"""A search within a genotype dataset"""