From 818797d2fd7e425da47cf12ddab6001e3234c2e5 Mon Sep 17 00:00:00 2001
From: Lei Yan
Date: Mon, 3 Feb 2014 12:48:37 -0600
Subject: On branch master

---
 wqflask/maintenance/dataset/db.py         | 10 ----------
 wqflask/maintenance/dataset/phenotypes.py | 27 +++++++++++++--------------
 wqflask/maintenance/dataset/utilities.py  | 10 ++++++++++
 3 files changed, 23 insertions(+), 24 deletions(-)
 delete mode 100644 wqflask/maintenance/dataset/db.py
 create mode 100644 wqflask/maintenance/dataset/utilities.py

(limited to 'wqflask/maintenance/dataset')

diff --git a/wqflask/maintenance/dataset/db.py b/wqflask/maintenance/dataset/db.py
deleted file mode 100644
index 453ee707..00000000
--- a/wqflask/maintenance/dataset/db.py
+++ /dev/null
@@ -1,10 +0,0 @@
-import MySQLdb
-
-def get_cursor():
-    host = 'localhost'
-    user = 'webqtl'
-    passwd = 'webqtl'
-    db = 'db_webqtl'
-    con = MySQLdb.Connect(db=db, host=host, user=user, passwd=passwd)
-    cursor = con.cursor()
-    return cursor
\ No newline at end of file
diff --git a/wqflask/maintenance/dataset/phenotypes.py b/wqflask/maintenance/dataset/phenotypes.py
index 821e5687..ae1e82cc 100644
--- a/wqflask/maintenance/dataset/phenotypes.py
+++ b/wqflask/maintenance/dataset/phenotypes.py
@@ -6,6 +6,8 @@ import os
 import re
 import MySQLdb
 
+import utilities
+
 def fetch():
     # parameters
     inbredsetid = 1
@@ -13,12 +15,7 @@ def fetch():
     #
     phenotypesfile.write("id\tOriginal_description\tPre_publication_description\tPost_publication_description\t")
     # open db
-    host = 'localhost'
-    user = 'webqtl'
-    passwd = 'webqtl'
-    db = 'db_webqtl'
-    con = MySQLdb.Connect(db=db, user=user, passwd=passwd, host=host)
-    cursor = con.cursor()
+    cursor = utilities.get_cursor()
     # get strain list
     strains = []
     sql = """
@@ -40,20 +37,22 @@ def fetch():
     phenotypesfile.flush()
     # phenotypes
     sql = """
-        SELECT PublishXRef.`Id`, Phenotype.`Original_description`, Phenotype.`Pre_publication_description`, Phenotype.`Post_publication_description`
-        FROM (PublishXRef, Phenotype)
-        WHERE PublishXRef.`PhenotypeId`=Phenotype.`Id`
-        AND PublishXRef.`InbredSetId`=%s
+        SELECT PublishXRef.`Id`, Publication.`Authors`, Phenotype.`Original_description`, Phenotype.`Pre_publication_description`, Phenotype.`Post_publication_description`
+        FROM (PublishXRef, Phenotype, Publication)
+        WHERE PublishXRef.`InbredSetId`=%s
+        AND PublishXRef.`PhenotypeId`=Phenotype.`Id`
+        AND PublishXRef.`PublicationId`=Publication.`Id`
         """
     cursor.execute(sql, (inbredsetid))
     results = cursor.fetchall()
     print "get %d phenotypes" % (len(results))
     for phenotyperow in results:
         publishxrefid = phenotyperow[0]
-        original_description = clearspaces(phenotyperow[1])
-        pre_publication_description = clearspaces(phenotyperow[2])
-        post_publication_description = clearspaces(phenotyperow[3])
-        phenotypesfile.write("%s\t%s\t%s\t%s\t" % (publishxrefid, original_description, pre_publication_description, post_publication_description))
+        authors = clearspaces(phenotyperow[1])
+        original_description = clearspaces(phenotyperow[2])
+        pre_publication_description = clearspaces(phenotyperow[3])
+        post_publication_description = clearspaces(phenotyperow[4])
+        phenotypesfile.write("%s\t%s\t%s\t%s\t" % (publishxrefid, authors, original_description, pre_publication_description, post_publication_description))
         sql = """
             SELECT Strain.Name, PublishData.value
             FROM (PublishXRef, PublishData, Strain)
diff --git a/wqflask/maintenance/dataset/utilities.py b/wqflask/maintenance/dataset/utilities.py
new file mode 100644
index 00000000..453ee707
--- /dev/null
+++ b/wqflask/maintenance/dataset/utilities.py
@@ -0,0 +1,10 @@
+import MySQLdb
+
+def get_cursor():
+    host = 'localhost'
+    user = 'webqtl'
+    passwd = 'webqtl'
+    db = 'db_webqtl'
+    con = MySQLdb.Connect(db=db, host=host, user=user, passwd=passwd)
+    cursor = con.cursor()
+    return cursor
\ No newline at end of file
-- 
cgit v1.2.3