Use DictCursor to fetch results into a dictionary

* wqflask/wqflask/db_info.py: Import DictCursor.
(InfoPage.get_info): Add aliases in SQL that will be used as keys
later by DictCursor..
(process_query_results): Delete it.

1 files changed, 55 insertions, 66 deletions

diff --git a/wqflask/wqflask/db_info.py b/wqflask/wqflask/db_info.py
index 9fcd1421..88c7aa2b 100644
--- a/wqflask/wqflask/db_info.py
+++ b/wqflask/wqflask/db_info.py
@@ -4,8 +4,10 @@ import urllib.error
 import urllib.parse
 import re
 
+from MySQLdb.cursors import DictCursor
 from wqflask.database import database_connection
 
+
 class InfoPage:
     def __init__(self, start_vars):
         self.info = None
@@ -18,29 +20,62 @@ class InfoPage:
         self.get_datasets_list()
 
     def get_info(self, create=False):
-        query_base = ("SELECT InfoPageName, GN_AccesionId, Species.MenuName, Species.TaxonomyId, Tissue.Name, InbredSet.Name, "
-                      "GeneChip.GeneChipName, GeneChip.GeoPlatform, AvgMethod.Name, Datasets.DatasetName, Datasets.GeoSeries, "
-                      "Datasets.PublicationTitle, DatasetStatus.DatasetStatusName, Datasets.Summary, Datasets.AboutCases, "
-                      "Datasets.AboutTissue, Datasets.AboutDataProcessing, Datasets.Acknowledgment, Datasets.ExperimentDesign, "
-                      "Datasets.Contributors, Datasets.Citation, Datasets.Notes, Investigators.FirstName, Investigators.LastName, "
-                      "Investigators.Address, Investigators.City, Investigators.State, Investigators.ZipCode, Investigators.Country, "
-                      "Investigators.Phone, Investigators.Email, Investigators.Url, Organizations.OrganizationName, "
-                      "InvestigatorId, DatasetId, DatasetStatusId, Datasets.AboutPlatform, InfoFileTitle, Specifics "
-                      "FROM InfoFiles "
-                      "LEFT JOIN Species USING (SpeciesId) "
-                      "LEFT JOIN Tissue USING (TissueId) "
-                      "LEFT JOIN InbredSet USING (InbredSetId) "
-                      "LEFT JOIN GeneChip USING (GeneChipId) "
-                      "LEFT JOIN AvgMethod USING (AvgMethodId) "
-                      "LEFT JOIN Datasets USING (DatasetId) "
-                      "LEFT JOIN Investigators USING (InvestigatorId) "
-                      "LEFT JOIN Organizations USING (OrganizationId) "
-                      "LEFT JOIN DatasetStatus USING (DatasetStatusId) WHERE ")
+        query_base = (
+            "SELECT InfoPageName AS info_page_name, "
+            "GN_AccesionId AS accession_id, "
+            "Species.MenuName AS menu_name, "
+            "Species.TaxonomyId AS taxonomy_id, "
+            "Tissue.Name AS tissue_name, "
+            "InbredSet.Name AS group_name, "
+            "GeneChip.GeneChipName AS gene_chip_name, "
+            "GeneChip.GeoPlatform AS geo_platform, "
+            "AvgMethod.Name AS avg_method_name, "
+            "Datasets.DatasetName AS dataset_name, "
+            "Datasets.GeoSeries AS geo_series, "
+            "Datasets.PublicationTitle AS publication_title, "
+            "DatasetStatus.DatasetStatusName AS dataset_status_name, "
+            "Datasets.Summary AS dataset_summary, "
+            "Datasets.AboutCases AS about_cases, "
+            "Datasets.AboutTissue AS about_tissue, "
+            "Datasets.AboutDataProcessing AS about_data_processing, "
+            "Datasets.Acknowledgment AS acknowledgement, "
+            "Datasets.ExperimentDesign AS experiment_design, "
+            "Datasets.Contributors AS contributors, "
+            "Datasets.Citation AS citation, "
+            "Datasets.Notes AS notes, "
+            "Investigators.FirstName AS investigator_firstname, "
+            "Investigators.LastName AS investigator_lastname, "
+            "Investigators.Address AS investigator_address, "
+            "Investigators.City AS investigator_city, "
+            "Investigators.State AS investigator_state, "
+            "Investigators.ZipCode AS investigator_zipcode, "
+            "Investigators.Country AS investigator_country, "
+            "Investigators.Phone AS investigator_phone, "
+            "Investigators.Email AS investigator_email, "
+            "Investigators.Url AS investigator_url, "
+            "Organizations.OrganizationName AS organization_name, "
+            "InvestigatorId AS investigator_id, "
+            "DatasetId AS dataset_id, "
+            "DatasetStatusId AS dataset_status_id, "
+            "Datasets.AboutPlatform AS about_platform, "
+            "InfoFileTitle AS info_file_title, "
+            "Specifics AS specifics"
+            "FROM InfoFiles "
+            "LEFT JOIN Species USING (SpeciesId) "
+            "LEFT JOIN Tissue USING (TissueId) "
+            "LEFT JOIN InbredSet USING (InbredSetId) "
+            "LEFT JOIN GeneChip USING (GeneChipId) "
+            "LEFT JOIN AvgMethod USING (AvgMethodId) "
+            "LEFT JOIN Datasets USING (DatasetId) "
+            "LEFT JOIN Investigators USING (InvestigatorId) "
+            "LEFT JOIN Organizations USING (OrganizationId) "
+            "LEFT JOIN DatasetStatus USING (DatasetStatusId) WHERE "
+        )
         if not all([self.gn_accession_id, self.info_page_name]):
             raise ValueError('No correct parameter found')
 
-        results = None
-        with database_connection() as conn, conn.cursor() as cursor:
+        results = {}
+        with database_connection() as conn, conn.cursor(DictCursor) as cursor:
             if self.gn_accession_id:
                 cursor.execute(f"{query_base}GN_AccesionId = %s",
                                (self.gn_accession_id,))
@@ -78,49 +113,3 @@ class InfoPage:
                 self.filelist.append([filename, filedate, filesize])
         except Exception as e:
             pass
-
-
-def process_query_results(results):
-    info_ob = {
-        'info_page_name': results[0],
-        'accession_id': results[1],
-        'menu_name': results[2],
-        'taxonomy_id': results[3],
-        'tissue_name': results[4],
-        'group_name': results[5],
-        'gene_chip_name': results[6],
-        'geo_platform': results[7],
-        'avg_method_name': results[8],
-        'dataset_name': results[9],
-        'geo_series': results[10],
-        'publication_title': results[11],
-        'dataset_status_name': results[12],
-        'dataset_summary': results[13],
-        'about_cases': results[14],
-        'about_tissue': results[15],
-        'about_data_processing': results[16],
-        'acknowledgement': results[17],
-        'experiment_design': results[18],
-        'contributors': results[19],
-        'citation': results[20],
-        'notes': results[21],
-        'investigator_firstname': results[22],
-        'investigator_lastname': results[23],
-        'investigator_address': results[24],
-        'investigator_city': results[25],
-        'investigator_state': results[26],
-        'investigator_zipcode': results[27],
-        'investigator_country': results[28],
-        'investigator_phone': results[29],
-        'investigator_email': results[30],
-        'investigator_url': results[31],
-        'organization_name': results[32],
-        'investigator_id': results[33],
-        'dataset_id': results[34],
-        'dataset_status_is': results[35],
-        'about_platform': results[36],
-        'info_file_title': results[37],
-        'specifics': results[38]
-    }
-
-    return info_ob