#!/usr/local/bin/python # -*- coding: iso-8859-15 -*- import os import sys import math import fnmatch import pyfits import numpy as np import stpydb import opusutil import glob import pyodbc from datetime import datetime from time import strftime from array import array from optparse import OptionParser ''' Updated by R. Diaz on Dec 1 2012 Changeed the script to deliver new Pysynphot files to the etc_file database after these are delivered to CDBS or even if they are not. The previous version uploaded all the files that were in CDBS. S Updated by R. Diaz on November 2013 added option only_master_update that will update the field for the name of the TMC that last used that file. This part was added to back populate the TMC file for old Synphot files. Usage: - To deliver the pysynphot, TMC, and TMG to the etc_files table, use the following command: synphot_upload.py '/hstdev/store/srefpipe/synphot/2012_11_19/' --delivery TRUE where the directory will change depending on the location. The script will ask questions like the date the files were delivered to CDBS, the version of the ETC software that started using them, the date when these were delivered to ETC, etc. One of the questions has to do with a new delivery number. If the option "no" is chosen (the default), it will look for the number in the above delivery. It will look for it in the name of the SQL files or the opus* files that the CDBS sendit script creates when it delivers the files to the CDBS database. However, for the cases when the file is not delivered to CDBS, like the non-hst bandpases, it can assign a new delivery number. - To update a field of the database: python synphot_upload.py '/hstdev/store/srefpipe/synphot/2012_11_19/' --update TRUE This is used on those cases where the date of delivery to ETC and the version of the ETC were not known when the files were delivered. Once this is known this option will populate these filds. Note that in this case the directory has to be the same directory from where the files were delivered to CDBS. If this is not available, create a list of the files that went into the delivery and feed it to the script as follows: python synphot_upload.py '/srefpipe/synphot/2012_11_19/' --update TRUE --input list_files.txt ''' # exceptions thrown class DBConnectionProblem(Exception): pass class TooManyDBErrors(Exception): pass class Base_db: """ ======================================================================= Class: Base_db Description: ------------ This class is a base class providing common function needed by all of the database-specific db query classes. Members: -------- _server = name of database server _dbname = name of database _DB = database object used to gain db access Methods: -------- connect - connects to the database close - closes the database connection zombie_select - run an SQL SELECT query listSqueeze - converts a doubly-dimensioned list ([][]) with only single elements in the 1st dimension to a singly dimensioned list, with each entry trimmed of trailing blanks ====================================================================== """ def __init__(self, server, dbname, notUsed=0): # Initialize members self._server = server self._dbname = dbname self._DB = None self.connect() """ ====================================================================== Name: connect Purpose: Connects to the database. ====================================================================== """ def connect(self): # connect to db try: self._DB = stpydb.stpydb(self._server, self._dbname) except: opusutil.PrintMsg("E","Failure to connect to database "+ self._server+":"+self._dbname) # rethrow so we get all possible information raise """ ====================================================================== Name: close Purpose: Closes an open database object. ======================================================================== """ def close(self): self._DB.close() """ ======================================================================= Name: zombie_select Description: ------------ Executes an SQL query and returns the result. Arguments: ---------- querytxt - the SQL text of the query to run result - structure for query results to be returned in Returns: a list of lists ======================================================================= """ def zombie_select(self, querytxt, result): # #opusutil.PrintMsg("D",querytxt) self._DB.query(querytxt) self._DB.executeAll(result) # return result """ ======================================================================= Name: update Description: ------------ Runs a database UPDATE query WITHOUT "chunking" the updates. Arguments: ---------- the_query - the SQL text of the UPDATE query to run Returns: none ======================================================================= """ def update(self, querytxt): # # define and run the query #opusutil.PrintMsg("D",querytxt) self._DB.query(querytxt) try: self._DB.executeUpdate() except TypeError: # a common occurence of this exception is when ZERO rows are # found to update, so let's ASSUME that happened until we find # a case that proves otherwise opusutil.PrintMsg("W","STPYDB exception most likely indicates ZERO updated rows from query: "+querytxt) return """ ======================================================================= Name: listSqueeze Description: ------------ A common DB query is to perform a select on a single field but get back more than one match. These come back from stpydb in a doubly-dimensioned list, where the last dimension of each list item is 1 (index=0). (e.g. results[[]] = result[0][0], result[1][0], result[2][0], etc. This utility will convert such a list into a singly-dimensioned list, with each entry trimmed of trailing space, to make it easier to work with. (e.g. result[0], result[1], result[2], etc.) Exceptions: ---------- An assertion exception is raised if the last dimension of each list item is not 1, as expected. Arguments: ---------- results - the list of lists that needs conversion Returns: --------- newlist - the singly dimensioned list created ======================================================================= """ def listSqueeze(self, results): # newlist = [] for anItem in results: assert len(anItem) == 1,("base_db::listSqueeze: length of "+ str(anItem)+" not 1") newlist.append(anItem[0].rstrip()) # return newlist def glob_all(top, pattern): allfiles = [] for path, dirs, files in os.walk(top): for filename in files: if fnmatch.fnmatch(filename, pattern): allfiles.append(os.path.join(path,filename)) allfiles.sort() return allfiles who = { 'ACS' : 'ACS/WFPC2 Team', 'NICMOS' : 'NICMOS Team', 'HST' : 'CRDS Team', 'STIS' : 'COS/STIS Team', 'COS' : 'COS/STIS Team', 'WFC3' : 'WFC3 Team', 'OTA' : 'Matt Lallo', 'NONHST' : 'CRDS Team', 'GRID' : 'CRDS Team', 'WFPC2' : 'ACS/WFPC2 Team', 'FGS' : 'Ed Nelan', 'FOS' : 'COS/STIS Team', 'HRS' : 'COS/STIS Team', 'FOC' : 'Warren Hack', 'HSP' : 'Rick White', 'WFPC' : 'ACS/WFPC2 Team', 'CALSPEC' : 'Ralph Bohlin', 'CALOBS' : 'Ralph Bohlin', 'OTHER' : '' } ############################################################# # Define database and server to use ############################################################# if os.environ["USER"] == 'srefpipe': datbase = 'GUMMO' tabledb= 'cdbs_ops' if os.environ["USER"] == 'cdbstest': datbase = 'GROUCHO' tabledb= 'cdbstest' #db_obj = Base_db('GUMMO','cdbs_ops') #db_obj = Base_db('GROUCHO','cdbstest') db_obj = Base_db(datbase,tabledb) def assign_delivery_num(new): ########################################################## # Open the delivery_number table, and extract the number.# # Convert it to a string, convert it to an integer, then # # increase it by 1. # ########################################################## # Comment 1.1 # # # # The behaviour will change for now. The delivery number # # should be the same as the delivery to CDBS. Once we # # start delivering to CRDS this number should increase by# # one # ########################################################## if new == 'y' or new == 'yes': db_obj.zombie_select('SELECT value from delivery_number',result) val = str(result).strip('[[]]') num = int(val) num += 1 else: input_delivery= glob.glob('*.sql') if not input_delivery: input_delivery= glob.glob('opus*') num = input_delivery[0][5:input_delivery[0].find('_')] else: num = input_delivery[0][13:input_delivery[0].find('.')] return num #++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ # Update #++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ def master_map(file1): ofile = pyfits.open(file1) data = ofile[1].data syn_files = data.field('filename') for fs in syn_files: c1=fs.find('$') c2=fs.find('fits') db_obj.update("UPDATE etc_file SET look_up_file='"+file1+"' WHERE filename='"+fs[c1+1:c2+4]+"'") #++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ def update_syn(dir_path, input_list = None, interactive ='y'): if dir_path[-1] != '/': dir_path = dir_path+'/' #Get current directory for cleanup at end cur_dir = os.getcwd() #Change directories to the dir_path specified in the call os.chdir(dir_path) columns_arr = [] check1 = raw_input('\n Do you want to update the date the files were delivered to the ETC?: (y/n) ') if check1 == 'y' or check1 == '': etc_delivery_date = raw_input('\n Enter the date the files were delivered to the ETC \n (e.g. Nov 1 2010 00:00:00, leave blank if none): ') columns_arr.append(" etc_delivery_date='") columns_arr.append(etc_delivery_date) check1 = 'continue_call' check2 = raw_input('\n Do you want to update the version of the ETC where the files where fist ingested?: (y/n) ') if check2 == 'y' or check2 == '': etc_test_version = raw_input('\n Enter ETC Test Version with which the files were tested (e.g. ETC 21.1rc2, leave blank if none): ') if check1 == 'continue_call': columns_arr.append("',") columns_arr.append(" etc_test_version='") columns_arr.append(etc_test_version) columns = ''.join(columns_arr); # Check if there are fits files in the dir_path directory if not input_list: input_list = glob.glob('*.fits') if not input_list: print '\n This direcotry has no fits files' check1 = raw_input('\n Do you want to deliver ETC dat and asc files? y/n defaul y ') if check1 == 'y' or check1 == '': input_list1 = glob.glob('*.dat') input_list2 = glob.glob('*asc') input_list = input_list1 + input_list2 else: quit() #Loops over all the files in the directory input_list.sort() print input_list for file1 in input_list: nomen = os.path.basename(file1) fname = [[]] db_obj.zombie_select("Select filename from etc_file where filename = '"+nomen+"'",fname) print 'fname =', fname if fname.count([nomen]) <= 0: print "'"+nomen+"' does not exits in the database." else: ################################################################################ # Update the etc_file table with date file deliverd to cdbs and the ETC version ################################################################################ db_obj.update("UPDATE etc_file SET "+columns+"' WHERE filename='"+nomen+"'") #++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ #++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ # Deliver the files to the database # Loop through the files, extract header values and other # # information to add to the database # #++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ def deliver_syn(dir_path, input_list = None, interactive = 'y'): etc_delivery_date = '' etc_delivery_date = raw_input('\n Enter the date the files were delivered to the ETC \n (e.g. Nov 1 2010 00:00:00, leave blank if none): ') etc_test_version = '' etc_test_version = raw_input('\n Enter ETC Test Version with which the files were tested (e.g. ETC 21.1rc2, leave blank if none): ') reason = 'Upload to ETC database' reason = raw_input('\n Enter the Reason for the delivery: ') syst = raw_input('\n Enter the systems that will use this file (e.g. PYSYNPHOT,ETC) default "PYSYNPHOT, ETC": ') if syst == '': syst = 'PYSYNPHOT, ETC' new = raw_input('\n Should a new delivery number be assigned? (default no) : ') synphot_delivery(dir_path, input_list, interactive, new, etc_delivery_date, etc_test_version, reason, syst) #++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ # only_master_update () # Loop through the TMC file and assign to the synphot files the TMC file that # recommended them #++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ def only_master_update(dir_path, input_file= None, input_list=None): if dir_path[-1] != '/': dir_path = dir_path+'/' #Get current directory for cleanup at end cur_dir = os.getcwd() #Change directories to the dir_path specified in the call os.chdir(dir_path) # Check if there are fits files in the dir_path directory isfits = False isascii = False if not input_file: input_list = glob.glob('*tmc.fits') if not input_list: print '\n This directory has no FITS files.' else: isfits = True else: if input_file.find('*') >= 0: input_list = glob.glob(input_file) input_file = None else: input_list = input_file ############################################################ # Loop through the TMC file ############################################################ ############################################################ # Loop through the files, extract header values and other # # information. # ############################################################ print len(input_list) input_list.sort() if input_file: file1 = input_list master_map(file1) print file1 else: for file1 in input_list: master_map(file1) print file1 #++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ # synphot_delivery() # Loop through the files, extract header values and other # # information to add to the database # #++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ def synphot_delivery(dir_path, input_list = None, interactive = 'y', new_delivnum = 'no', etc_delivery_date = None, etc_test_version = None, reason = None, syst = None): if dir_path[-1] != '/': dir_path = dir_path+'/' #Get current directory for cleanup at end cur_dir = os.getcwd() #Change directories to the dir_path specified in the call os.chdir(dir_path) # Check if there are fits files in the dir_path directory isfits = False isascii = False if not input_list: input_list = glob.glob('*.fits') if not input_list: print '\n This directory has no FITS files.' check1 = raw_input('\n Do you want to deliver ETC dat and asc files? (y/n default yes): ') if check1 == 'y' or check1 == '': input_list1 = glob.glob('*.dat') input_list2 = glob.glob('*asc') input_list = input_list1 + input_list2 isascii = True else: quit() else: isfits = True ################################################# # Get delivery number # ################################################# num = assign_delivery_num(new_delivnum) ############################################################ # Loop through the files, extract header values and other # # information. # ############################################################ #Type of models model_map = ['agn','bpgs','etc_models', 'gunnstryker','kc96', 'bc95', 'bz77', 'extinction','jacobi','phoenix', 'bkmodels', 'ck04models', 'galactic', 'k93models', 'pickles'] model_ftype= ['The AGN Atlas','Gunn and Stryker 1983 Atlas', 'ETC Data models', 'Gunn-Stryker atlas by K. Horne', 'Kinney-Calzetti Spectral Atlas of Galaxies', 'Bruzual-Charlot Atlas', 'King-Bruzual Atlas', 'Extiction files','The Jocobi-Christenson Atlas', 'Phoenix Models','Buser-Kurucz Models', 'Castelli-Kurucz Models','Galactic Emission Line Object Atlas', 'Kurucz 1993 Models', 'Stellar Flux Library by Pickels '] model = raw_input('\n Please, select the type of file you are delivering (grid, throughput, master): ') if model == 'grid': model1 = raw_input('\n Please, select the atlas: (agn,bpgs,etc_models,gunnstryker,kc96,bc95,bz77,extinction,jacobi,phoenix,bkmodels,ck04models, galactic,k93models,pickles) ') input_list.sort() for file1 in input_list: print file1 t = datetime.now() toodae = t.strftime('%b %d %Y %H:%M:%S') nomen = os.path.basename(file1) if isfits: hdu = pyfits.open(file1,ignore_missing_end=True) prihdr = hdu[0].header ####################################################### # Descriptors for each of the file types ####################################################### if 'DBTABLE' in prihdr: ftype = prihdr['DBTABLE'] else: ftype = raw_input('\n DBTABLE is blank, please provide file_type value (CRGRAPH, CRCOMPLIST, CRTHERMLIST, CRGRID, CRSPECTRUM)?:') # Some DBTABLE keywords have = as part of the string so # we need to remove it. fchk = ftype.find('=') if fchk == 0: fch0 = ftype.strip() fch=fch0.replace('= \'','') ftype = fch.replace('\'','') if ftype == 'CRGRAPH': # Graph component files mkey = 'GRAPH' elif ftype == 'CRCOMPLIST': # Master component files mkey = 'MASTERCOMP' elif ftype == 'CRTHERMLIST': # Master Emission files mkey = 'MASTERTHERMAL' elif ftype == 'CRGRID' or ftype == 'crgrid' or ftype == 'CRGRIDS': st1=model_map.index(model1) mkey = model_map[st1] ftype = model_ftype[st1] elif ftype == 'CRSPECTRUM' or ftype == 'crspectrum': mkey = 'CALSPEC' #mkey = 'CALOBS' ftype = 'HST Stellar Standard Spectra' elif 'FILE_TYP' in prihdr: mkey = ftype ftype = prihdr['FILE_TYP'] else: mkey = prihdr['COMPNAME'] if mkey == 'CALSPEC': instr = 'CALSPEC' if mkey == 'CALOBS': instr = 'CALOBS' elif 'INSTRUME' in prihdr: instr = prihdr['INSTRUME'] else: instr = 'GRID' print 'instrument ====',instr contact = who[instr.upper()] if 'USEAFTER' in prihdr: dayt = prihdr['USEAFTER'] else: dayt = 'Jan 1 1997' dait = [[]] db_obj.zombie_select("Select convert(varchar(11),general_availability_date) from synphot_file where file_name='"+nomen+"'",dait) dayt = str(dait).strip('[[]]') if isascii: keywords = ['MAPKEY','CONTACT','FILE_TYP','REASON','SYSTEMS','CREATED'] datfile = open(file1) filedata=[] for line in datfile: filedata.append(line) values = [] for key in keywords: for line in filedata[0:30]: n1 = line[1:].find(key) if n1 != -1: #print line l1 = line.replace('\n','') values.append(l1[n1+11:]) if len(values) != len(keywords): print 'One of the keyword was not found' mkey = values[0] ftype = values[2] filename = file1 inst = ' ' contact = values[1] reason = values[3] syst = values[4] dayt = "'" + values[5].strip() + "'" if dayt == '': dait = [['Apr 1 1800']] dayt = str(dait).strip('[[]]') ################################################################################ # Check to see if the file already exists in the database. If it is, notify the# # user and move to the next file. # ################################################################################ fname = [[]] db_obj.zombie_select("Select filename from etc_file where filename = '"+nomen+"'",fname) if fname.count([nomen]) > 0: print "'"+nomen+"' already exits in the database." else: ################################################################################ # Find the values of mapkey in etc_file. Check to see if the value of mapkey # # of the file being added is already in etc_file. If it is, set the value # # of active for the files in etc_file to 'F'; the value of active for the file # # being added will be 'T'. # ################################################################################ active = 'T' key=[[]] db_obj.zombie_select('Select mapkey from etc_file',key) if key.count([mkey]) > 0: db_obj.update("UPDATE etc_file SET active = 'F' where mapkey = '"+mkey+"'") ################################################################################ # Update the etc_file table with FILENAME, DELIVERY_DATE, DELIVERY_NUMBER, # # MAPKEY, CONTACT, CREATED, FILE_TYPE, ACTIVE, REASON, and SYSTEMS information.# ################################################################################ db_obj.update("INSERT etc_file (filename, delivery_date, delivery_number, etc_delivery_date, etc_test_version, mapkey, contact, created, file_type, active, reason_for_delivery, pysyn_test_version, systems) VALUES ('"+nomen +"','"+toodae +"','"+str(num) +"','"+etc_delivery_date +"','"+etc_test_version +"','"+mkey +"','"+contact +"',"+dayt +",'"+ftype +"','"+active +"','"+reason +"','0.9.2','"+str(syst) +"')") print 'filename=',nomen, ' delivery date =', toodae, ' delivery number =',str(num), ' etc delivery date =',etc_delivery_date,\ ' etc_test_version=',etc_test_version,' mkey=',mkey,' contact =',contact,' dayt=',dayt,' ftype=',ftype,' active=',active,\ ' reason =',reason if mkey == 'MASTERCOMP': master_map(file1) if new_delivnum == 'y': #if new_delivnum == 'y' or new_delivnum == '': #################################### # Update the delivery_number table # #################################### db_obj.update("UPDATE delivery_number SET value = "+str(num)) if __name__ == "__main__": db_obj.connect() result = [[]] parser = OptionParser() parser.add_option('--input', dest = 'input_list', help = 'List of input files, default: all files in directory', default = None) parser.add_option('--in_file', dest = 'input_file', help = 'Filename to use. Default is None ', default = None) parser.add_option('-i', dest = 'interactive', help = 'Run program interactively?', default = 'y') parser.add_option('--history', dest = 'input_history', help = 'Instrument and component to add to the history of the TMC table', default = None) parser.add_option('--delivery', dest = 'step1', help = 'Would you like to upload new files to the database?', default = False) parser.add_option('--update', dest = 'step2', help = 'Would you like to update old files already in the database?', default = False) parser.add_option('--assign_master', dest = 'step3', help = 'Would you like to update the name of the TMC file that last used the Synphot files?', default = False) (options, args) = parser.parse_args() if options.step1: deliver_syn(args[0], options.input_list, options.interactive) elif options.step2: update_syn(args[0], options.input_list, options.interactive) elif options.step3: only_master_update(args[0], options.input_file) else: print " Add to the call --delivery True or --update optioni True or --assign_master True" db_obj.close() print 'Delivery DONE'