Changeset 31178 for trunk/ippToPsps/jython/batch.py

Timestamp:

Apr 5, 2011, 3:02:30 PM (15 years ago)

Author:

rhenders

Message:

Added methods to report NULL columns as well replace NULLs with a substitute (necessary for crazy PSPS -999 NULL thing); changed constructor arguments

File:

• trunk/ippToPsps/jython/batch.py (modified) (9 diffs)

trunk/ippToPsps/jython/batch.py

@@ -4 +4 @@
 import datetime
 import re
+import sys
+
+from gpc1 import Gpc1
 
 from java.lang import *
 from java.sql import *
-
-
+from xml.etree.ElementTree import ElementTree
+
+
+'''
+Base class of all batch types.
+'''
 class Batch(object):
 
@@ -15 +22 @@
     '''
     Constructor
-    '''
-    def __init__(self, batchType, inputFitsPath, outputFitsPath, dbHost, dbName, dbUser, dbPass, survey=""):
+
+    >>> batch = Batch(1,2,3,4,5,6,7)
+    >>> print batch.pspsVoTableFilePath
+    "../config/2/tables.vot"
+    '''
+    def __init__(self, batchType, inputFitsPath, survey=""):
 
         # set up class variables
         self.pspsVoTableFilePath = "../config/" + batchType + "/tables.vot"
         self.inputFitsPath = inputFitsPath
-        self.outputFitsPath = outputFitsPath
-        self.dbHost = dbHost
-        self.dbName = dbName
-        self.dbUser = dbUser
-        self.dbPass = dbPass
         self.survey = survey
 
-        # set up JDBC connection
-        self.url = "jdbc:mysql://"+self.dbHost+"/"+self.dbName+"?user="+self.dbUser+"&password="+self.dbPass
-        self.con = DriverManager.getConnection(self.url)
-        self.stmt = self.con.createStatement()
+        # open config
+        doc = ElementTree(file="config.xml")
+
+        # set up JDBC connection to local Db
+        dbName = doc.find("localdatabase/name").text
+        dbHost = doc.find("localdatabase/host").text
+        dbUser = doc.find("localdatabase/user").text
+        dbPass = doc.find("localdatabase/password").text
+        self.localUrl = "jdbc:mysql://"+dbHost+"/"+dbName+"?user="+dbUser+"&password="+dbPass
+        self.localCon = DriverManager.getConnection(self.localUrl)
+        self.localStmt = self.localCon.createStatement()
+
+        # create Gpc1 object
+        self.gpc1 = Gpc1()
+
+        # set up JDBC connection to gpc1 Db
+        dbName = doc.find("gpc1database/name").text
+        dbHost = doc.find("gpc1database/host").text
+        dbUser = doc.find("gpc1database/user").text
+        dbPass = doc.find("gpc1database/password").text
+        self.gpc1Url = "jdbc:mysql://"+dbHost+"/"+dbName+"?user="+dbUser+"&password="+dbPass
+        self.gpc1Con = DriverManager.getConnection(self.localUrl)
+        self.gpc1Stmt = self.localCon.createStatement()
 
         # get survey ID from init table
         sql = "SELECT surveyID from Survey WHERE name = '" + survey + "'"
         try:
-            rs = self.stmt.executeQuery(sql)  
+            rs = self.localStmt.executeQuery(sql)  
             rs.first()
             self.surveyID = rs.getInt(1)
@@ -56 +81 @@
 
         self.log("Batch destructor")
-        self.stmt.close()
-        self.con.close()
+        self.localStmt.close()
+        self.localCon.close()
+        self.gpc1Stmt.close()
+        self.gpc1Con.close()
 
     '''
@@ -72 +99 @@
 
         sql = "UPDATE " + table + "  SET surveyID=%d" % self.surveyID
-        self.stmt.execute(sql)
+        self.localStmt.execute(sql)
 
     '''
@@ -80 +107 @@
 
         sql = "UPDATE "+table+" AS a, Filter AS b SET a.filterID=b.filterID WHERE b.filterType = '" + self.filter + "'"
-        self.stmt.execute(sql)
+        self.localStmt.execute(sql)
 
     '''
@@ -130 +157 @@
          for table in self.pspsTables:
              self.log("Creating PSPS table: " + table.name)
-             table.write(self.url + '#' + table.name)
+             table.write(self.localUrl + '#' + table.name)
 
          self.indexPspsTables()
@@ -150 +177 @@
         sql = "CREATE INDEX "+table+"_index ON "+table+" ("+column+")"
         try:
-            self.stmt.execute(sql)
+            self.localStmt.execute(sql)
         except:
             self.log("Index already in place on '" + column + "' for table '" + table + "'")
@@ -178 +205 @@
           table = stilts.tpipe(table, cmd='explodeall')
           try:
-              table.write(self.url + '#' + table.name)
+              table.write(self.localUrl + '#' + table.name)
           except:
               self.log("ERROR problem writing table '" + table.name + "' to the database")
@@ -198 +225 @@
         self.log("    Selecting database tables")
         for table in self.pspsTables:
-           _table = stilts.tread(self.url + '#SELECT * FROM ' + table.name)
+           _table = stilts.tread(self.localUrl + '#SELECT * FROM ' + table.name)
            _table = stilts.tpipe(_table, cmd='tablename ' + table.name)
            _tables.append(_table)
 
-        self.log("    Writing to FITS file " + self.outputFitsPath)
+        self.log("    Writing to FITS file '" + self.outputFitsPath + "'...")
         stilts.twrites(_tables, self.outputFitsPath, fmt='fits')
-
+        self.log("    ...done")
+
+    '''
+    Returns a list of column names for this table
+    '''
+    def getColumnNames(self, tableName):
+
+       sql = "SHOW COLUMNS FROM " + tableName
+       rs = self.localStmt.executeQuery(sql)
+       columns = []
+       while (rs.next()): columns.append(rs.getString(1))
+       rs.close()
+       
+       return columns
+
+    '''
+    Replaces all NULL values in the provided table with the prvoded substitute 
+    '''
+    def replaceNulls(self, tableName, sub):
+
+       # get list of columns
+       columns = self.getColumnNames(tableName)
+
+       # now loop through all columns and replace all NULLs with sub
+       for column in columns:
+          
+          sql = "UPDATE " + tableName + " SET " + column + " = " + sub + " WHERE " + column + " IS NULL"
+          self.localStmt.execute(sql)
+
+
+    '''
+    Searches a table and reports the columns that are either partially or completely populated with NULLs
+    '''
+    def reportNulls(self, tableName, showPartials):
+
+       # first, count rows
+       sql = "SELECT COUNT(*) FROM " + tableName
+       rs = self.localStmt.executeQuery(sql)
+       rs.first()
+       numRows = rs.getInt(1)
+
+       # get list of columns
+       columns = self.getColumnNames(tableName)
+
+       print "+----------------------+---------------+"
+       print "|  %25s           |" % tableName
+       print "+----------------------+---------------+"
+
+       # now see which columns are full of NULLS, with are partially NULL
+       for column in columns:
+          
+          sql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + column + " IS NULL"
+          rs = self.localStmt.executeQuery(sql)
+          rs.first()
+          if rs.getInt(1) == numRows:
+              print "| %20s | all NULL      |" % column
+          elif showPartials and rs.getInt(1) > 0:
+              print "| %20s | partial NULL  |" % column
+       rs.close()
+       print "+----------------------+---------------+"
+
+
+    '''
+    Searches all PSPS tables and reports the columns that are either partially or completely populated with NULLs
+    '''
+    def reportNullsInAllPspsTables(self, showPartials):
+
+        for table in self.pspsTables:
+            self.reportNulls(table.name, showPartials)
+
+    '''
+    Searches all PSPS tables and replaces all NULLs with the provided substitute
+    '''
+    def replaceAllPspsNulls(self, sub):
+
+        self.log("Replacing all NULL values in PSPS tables with '" + sub + "'...")
+        for table in self.pspsTables:
+            self.replaceNulls(table.name, sub)
+        self.log("...done")
 
     '''