Changeset 31965 for trunk/ippToPsps/jython/dvoToMySQL.py

Timestamp:

Aug 1, 2011, 4:05:53 PM (15 years ago)

Author:

rhenders

Message:

finally cleared up image_id/extern_id confusion

File:

• trunk/ippToPsps/jython/dvoToMySQL.py (modified) (11 diffs)

trunk/ippToPsps/jython/dvoToMySQL.py

@@ -10 +10 @@
 from subprocess import call, PIPE, Popen
 
-from gpc1db import Gpc1Db
+from pslogger import PSLogger
 from scratchdb import ScratchDb
 
@@ -27 +27 @@
 
     '''
-    def __init__(self, logger, pathToDvo):
+    def __init__(self, logger):
 
         # set up logging
         self.logger = logger
-        self.pathToDvo = pathToDvo
-        self.logger.debug("DvoToMySql class constructor")
-
-        self.logger.debug("Important DVO database at " + self.pathToDvo)
+        self.logger.infoSeparator()
+        self.logger.info("Started dvoToMySql")
+        self.dvoMetaTable = "dvoMetaFull"
+        self.dvoDetectionTable = "dvoDetectionFull"
 
         # open config
-        doc = ElementTree(file="config.xml")
+        self.doc = ElementTree(file="config.xml")
+        self.dvoLocation = self.doc.find("dvo/location").text
+
+        # some log stuff
+        self.logger.infoPair("Using DVO meta table", self.dvoMetaTable)
+        self.logger.infoPair("Using DVO detection table", self.dvoDetectionTable)
+        self.logger.infoPair("Importing DVO database at", self.dvoLocation)
 
         # create database objects
-        self.scratchDb = ScratchDb(logger)
-        self.gpc1Db = Gpc1Db(self.logger)
+        self.scratchDb = ScratchDb(logger, self.doc, 1)
 
         # create DVO tables
@@ -47 +52 @@
 
         # import Images.dat table
+        self.logger.infoPair("Deleting from table", self.dvoMetaTable)
         sql = "DELETE FROM dvoMetaFull"
-        self.scratchDb.stmt.execute(sql)
-
-        imagesTableName = self.importFits(self.pathToDvo, 
+        self.scratchDb.execute(sql)
+
+        imagesTableName = self.importFits(self.dvoLocation, 
                 "", 
                 "Images.dat", 
-                "IMAGE_ID SOURCE_ID CCDNUM EXTERN_ID FLAGS PHOTCODE NSTAR")
-        self.scratchDb.createIndex(imagesTableName, "IMAGE_ID")
+                "SOURCE_ID IMAGE_ID CCDNUM EXTERN_ID FLAGS PHOTCODE NSTAR")
+        self.scratchDb.createIndex(imagesTableName, "EXTERN_ID")
 
         # insert into dvoMetaFull
+        # NB what we and smf files call IMAGE_ID, DVO calls EXTERN_ID. We are sticking
+        # with the smf name
         self.logger.info("Inserting all image meta data into database")
-        sql = "INSERT INTO dvoMetaNew ( \
+        sql = "INSERT INTO dvoMetaFull ( \
                sourceID, \
                imageID, \
@@ -71 +79 @@
                PHOTCODE \
                FROM " + imagesTableName
-        self.scratchDb.stmt.execute(sql)
+        self.scratchDb.execute(sql)
 
         subdirs = ['n0000']
@@ -77 +85 @@
         for subdir in subdirs:
 
-            files = glob.glob(pathToDvo + "/" + subdir + "/*.cpm")
+            files = glob.glob(self.dvoLocation + "/" + subdir + "/*.cpm")
 
             #files = ['0247.06', '0244.06', '0244.10']
@@ -90 +98 @@
 
                 # import cpm table and index
-                cpmTableName = self.importFits(self.pathToDvo, 
+                cpmTableName = self.importFits(self.dvoLocation, 
                         subdir, 
                         file + ".cpm", 
@@ -98 +106 @@
 
                 # import cpt table and index
-                cptTableName = self.importFits(self.pathToDvo, 
+                cptTableName = self.importFits(self.dvoLocation, 
                         subdir, 
                         file + ".cpt", 
@@ -107 +115 @@
                 # shove SOURCE_IDs into measurement table
                 self.logger.info("Adding SOURCE_IDs into measurements table")
-                sql = "ALTER TABLE "+cpmTableName+" ADD COLUMN (SOURCE_ID SMALLINT)"
-                self.scratchDb.stmt.execute(sql)
-                sql = "UPDATE "+cpmTableName+" AS a, "+imagesTableName+" AS b \
+                sql = "ALTER TABLE " + cpmTableName + " ADD COLUMN (SOURCE_ID SMALLINT)"
+                self.scratchDb.execute(sql)
+                sql = "UPDATE " + cpmTableName + " AS a, " + imagesTableName + " AS b \
                        SET a.SOURCE_ID = b.SOURCE_ID \
                        WHERE a.IMAGE_ID = b.IMAGE_ID"
-                self.scratchDb.stmt.execute(sql)
+                self.scratchDb.execute(sql)
 
                 # shove PSPS objID in measurement table
                 self.logger.info("Adding PSPS objID into measurements table")
                 sql = "ALTER TABLE "+cpmTableName+" ADD COLUMN (PSPS_OBJ_ID BIGINT)"
-                self.scratchDb.stmt.execute(sql)
+                self.scratchDb.execute(sql)
                 sql = "UPDATE "+cpmTableName+" AS a, "+cptTableName+" AS b \
                        SET a.PSPS_OBJ_ID = b.EXT_ID \
                        WHERE a.CAT_ID = b.CAT_ID \
                        AND a.OBJ_ID = b.OBJ_ID" 
-                self.scratchDb.stmt.execute(sql)
-
-                self.logger.info("Putting everything into dvoDetectionFull table")
+                self.scratchDb.execute(sql)
+
+                self.logger.infoPair("Populating", self.dvoDetectionTable)
                 sql = "INSERT IGNORE INTO dvoDetectionFull (\
                        sourceID \
@@ -142 +150 @@
                        ,DB_FLAGS \
                        FROM " + cpmTableName
-                self.scratchDb.stmt.execute(sql)
+                self.scratchDb.execute(sql)
 
                 # now drop what we don't need
@@ -173 +181 @@
       tableName = tableName.replace('.', '_')
 
-      self.logger.info("Attempting to import tables from '" + fullPath + "' to '" + tableName + "'")
+      self.logger.infoPair("Importing tables from file", fullPath)
+      self.logger.infoPair("Writing to database table", tableName)
 
       tables = stilts.treads(fullPath)
@@ -195 +204 @@
           count = count + 1
 
-      self.logger.info("Done. Imported %d tables" % count)
+      self.logger.infoPair("Finished importing", "%d tables" % count)
 
       return tableName
 
 logging.config.fileConfig("logging.conf")
-logger = logging.getLogger("dvotomysql")
-logger.info("Starting")
-
-dvoToMySql = DvoToMySql(logger, "/data/ipp005.0/gpc1/catdirs/MD04.merges/MD04.merge")
-#dvoToMySql = DvoToMySql(logger, "/export/ippc00.1/rhenders/MD04.merge")
-
-logger.info("Program complete")
-
+logging.setLoggerClass(PSLogger)
+logger = logging.getLogger("dvoToMySQLLog")
+logger.setLevel(logging.INFO)
+
+
+dvoToMySql = DvoToMySql(logger)
+
+logger.infoPair("Program...", "complete")
+