Changeset 34949

Timestamp:

Jan 17, 2013, 6:11:44 AM (14 years ago)

Author:

eugene

Message:

fix alter table sql (need "add column" before each new entry), fix update sql (mixed up ave and meas, renamed a & b to ave & meas for clarify), replace DEC with DEC_ in sql

File:

• branches/eam_branches/ipp-20121219/ippToPsps/jython/dvodetections.py (modified) (2 diffs)

branches/eam_branches/ipp-20121219/ippToPsps/jython/dvodetections.py

@@ -83 +83 @@
        # first try to add the new columns. catch failure and continue
        try:
-           sql = "ALTER TABLE " + cpmTableName + " ADD COLUMN (PSPS_OBJ_ID BIGINT), (ZP REAL), (RA FLOAT), (DEC FLOAT)"
+           sql = "ALTER TABLE " + cpmTableName + " ADD COLUMN (PSPS_OBJ_ID BIGINT), ADD COLUMN (ZP REAL), ADD COLUMN (RA FLOAT), ADD COLUMN (DEC_ FLOAT)"
            self.scratchDb.execute(sql)
        except:
-           self.logger.infoPair("Already added PSPS_OBJ_ID column to", cpmTableName)
+           # failed, but may be due to mysql error
+           self.logger.infoPair("mysql error or already added PSPS_OBJ_ID column to", cpmTableName)
+           # self.logger.infoPair("sql command", sql)
                
        # shove PSPS objIDs from cpt table and the calibrated zero point and coordinates which are formed 
        # by combining values from multiple tables into cpt table
-       sql = "UPDATE " + cpmTableName + " AS a, " + cptTableName + " AS b, " \
-              + self.scratchDb.dvoPhotcodesTable + " AS c \
-              SET a.PSPS_OBJ_ID = b.EXT_ID, \
-              b.ZP  = c.C_LAM * 0.001 + c.K * (b.AIRMASS - 1) - b.M_CAL, \
-              b.RA  = a.RA  - (b.D_RA  / 3600.0), \
-              b.DEC = a.DEC - (b.D_DEC / 3600.0) \
-              WHERE a.CAT_ID = b.CAT_ID \
-              AND a.OBJ_ID = b.OBJ_ID   \
-              AND b.PHOTCODE = c.CODE"
+       sql = "UPDATE " \
+              + cpmTableName + " AS meas, " \
+              + cptTableName + " AS ave, " \
+              + self.scratchDb.dvoPhotcodesTable + " AS phot \
+              SET \
+              meas.PSPS_OBJ_ID = ave.EXT_ID, \
+              meas.ZP  = phot.C_LAM * 0.001 + phot.K * (meas.AIRMASS - 1) - meas.M_CAL, \
+              meas.RA  = ave.RA  - (meas.D_RA  / 3600.0), \
+              meas.DEC_ = ave.DEC_ - (meas.D_DEC / 3600.0) \
+              WHERE meas.CAT_ID = ave.CAT_ID \
+              AND meas.OBJ_ID = ave.OBJ_ID   \
+              AND meas.PHOTCODE = phot.CODE"
 
+       self.logger.infoPair("sql command", sql)
        self.scratchDb.execute(sql)
 
@@ -136 +142 @@
               ,POW(10.0, 0.4 * M_TIME)  \
               ,RA   \
-              ,DEC  \
+              ,DEC_  \
               ,X_CCD_ERR*PLTSCALE   \
               ,Y_CCD_ERR*PLTSCALE   \