def main(featList): newLoad = False print 'Begin creating new features from wtg.encounter' # truncate mmi_wtg staging table print 'Truncating wtg.encounter_stage...' truncateWtgStaging() # If not passed in, find a list of the new featureids if featList == []: newLoad = True param = {} sql = dbutil.sqlArgosImport.getAllEncounters result = dbutil.dbSelect(conn, sql, param) featList = [f[0] for f in result] #== get from FeatureList.csv instead! # copy new records to staging table print 'Appending wtg.encounter_stage...' fillStaging(sorted(featList)) # create temporary XY layer print 'Creating in_memory XY event layer...' scratchFile = makeXY() #truncate SDE Encouter_Stage print 'Truncating sde.encounter_stage...' truncateSdeStaging() if newLoad: print 'WARNING!! Truncating sde.encounter...' truncateSdeEncounter() # copy features to staging featureclass print "Appending sde.encounter_stage..." copyToStage(scratchFile) # insert staging features to Encounter print "Loading sde.encounter from staging..." loadEncounter()
def fillStaging(featList): param = {'feats': tuple(featList)} sql = dbutil.sqlArgosImport.appendStaging try: result = dbutil.dbSelect(conn, sql, param) #objsList = [f[0] for f in result] except Exception as e: print 'Error appending wtg.encounter_stage: ' + e.message conn.rollback() finally: result = None conn.commit() print '...Done'
def newBehaviorData(ptt,devID,inFileName): newData = [] behDict = db_util.sqlMeasData.behavDict with open(inFileName, 'rb') as inFile: reader = csv.DictReader(inFile) for row in reader: beh = row['What'] if beh == 'Message': # find the message time to match with DeviceTransmit # THIS IS THE WRONG TIMEVAL!!! # Maybe record the time of the behavior, don't link to a transmit timeVal = util.getDateFromSerial(float(row['Start'])) param = {'device': devID, 'timeVal':timeVal} # getTransmits returns a list of tuples[(measID,txID),(measID,txID)] featList = db_util.dbSelect(conn, db_util.sqlMeasData.getTransmits, param) if len(featList)<1: print "getStatus error: no transmit found for "+timeVal.strftime('%Y-%m-%d %H:%M:%S') # if multiple transmits found, only use the last one measID, txID = featList[-1] elif beh == 'Dive': for key in behDict.keys(): if row[key]: try: measObj=tables.MeasuredData(behDict[key][0], # ParameterID row[key], # DataValue behDict[key][1], # ValueDomain behDict[key][2], # DomainID None, # Invalid devID, # DeviceID '', # MeasureType None, # TypeID measID) # MeasurementID measID = db_util.dbTransact(conn,measObj.queryText(),measObj.insertSQL()) except Exception as e: print 'newBehaviorData error: ' + e.message conn.rollback() finally: measObj = None conn.commit() newData.append(measuredID) else: pass return newData
def newStatusData(ptt,devID,inFileName): newData = [] statDict = db_util.sqlMeasData.statusDict with open(inFileName, 'rb') as inFile: reader = csv.DictReader(inFile) for row in reader: timeVal = util.getDateFromSerial(float(row['Received'])) param = {'device': devID, 'timeVal':timeVal} # getTransmits returns a list of tuples[(measID,txID),(measID,txID)] featList = db_util.dbSelect(conn, db_util.sqlMeasData.getTransmits, param) if len(featList)<1: print "getStatus error: no transmit found for "+timeVal.strftime('%Y-%m-%d %H:%M:%S') # if multiple transmits found, only use the last one measID, txID = featList[-1] for key in statDict.keys(): if row[key]: try: measObj=tables.MeasuredData(statDict[key][0], # ParameterID row[key], # DataValue statDict[key][1], # ValueDomain statDict[key][2], # DomainID None, # Invalid devID, # DeviceID 'devicetransmit', # MeasureType txID, # TypeID measID) # MeasurementID measID = db_util.dbTransact(conn,measObj.queryText(),measObj.insertSQL()) except Exception as e: print 'newStatusData error: ' + e.message conn.rollback() finally: measObj = None conn.commit() newData.append(measID) return newData
def insertDeploys(): conn = dbutil.getDbConn('mmi_wtg') sql = dbutil.sqlDeployments.insertDeploys deploys = dbutil.dbSelect(conn, sql, ()) return deploys
def delDuplicates(features): # ======== Hangs on single FeatureID b/c of parenthesis in tuple # =========TODO: convert to proper parameter passing!!!================ # 1 fetch MeasurementID's sql = dbutil.sqlDuplicates.getMeasurements if len(features) == 1: param = '(' + str(features[0]) + ')' else: param = tuple(features) sql = sql + str(param) + ';' result = dbutil.dbSelect(conn, sql, ()) measList = [f[0] for f in result] if len(measList) > 0: # 2 DEL from DeviceTransmit sql = dbutil.sqlDuplicates.delDevTransmit if len(features) == 1: param = '(' + str(measList[0]) + ')' else: param = tuple(measList) sql = sql + str(param) + ';' result = dbutil.dbDelete(conn, sql) if result == 'Failed': print 'Delete from DeviceTransmit failed' conn.rollback() sys.exit else: print result conn.commit() # 3 DEL from Measurement sql = dbutil.sqlDuplicates.delMeasurement if len(features) == 1: param = '(' + str(features[0]) + ')' else: param = tuple(features) sql = sql + str(param) + ';' result = dbutil.dbDelete(conn, sql) if result == 'Failed': print 'Delete from Measurement failed' conn.rollback() sys.exit else: print result conn.commit() else: print 'No Measurements found' # 4 DEL from ArgosPass sql = dbutil.sqlDuplicates.delArgosPass if len(features) == 1: param = '(' + str(features[0]) + ')' else: param = tuple(features) sql = sql + str(param) + ';' result = dbutil.dbDelete(conn, sql) if result == 'Failed': print 'Delete from ArgosPass failed' conn.rollback() sys.exit else: print result conn.commit() # 5 DEL duplicate encounters sql = dbutil.sqlDuplicates.delEncounter if len(features) == 1: param = '(' + str(features[0]) + ')' else: param = tuple(features) sql = sql + str(param) + ';' result = dbutil.dbDelete(conn, sql) if result == 'Failed': print 'Delete from Encounter failed' conn.rollback() sys.exit else: print result conn.commit() print '...Done with deletions'
def getDuplicates(tagDict, newFeats): """Return a list of duplicate featureid's to delete from encounter and write them to a log file""" featIDs = [] skipID = [] logger = [] for device, vals in sorted(tagDict.items()): # sorted on DevID ptt = str(vals[0]) # int yr = str(vals[7]) # int cruise = vals[8] # str outFile = outPath + '\\' + yr + '\\' + cruise + '\\DuplicatesDeleted.csv' print "Processing duplicates for PTT: " + ptt param = {'devID': device, 'featList': tuple(newFeats)} featList = dbutil.dbSelect(conn, dbutil.sqlDuplicates.getPasses, param) # iter over features to find dupes for tup in featList: featID = tup[0] if featID in skipID: skipID.remove(featID) continue # if lastID was the duplicate skip it tag = tup[1][:-9] timeVal = tup[2] sat = tup[3] nbMes = tup[4] timeStart = timeVal + datetime.timedelta(minutes=-6) timeStop = timeVal + datetime.timedelta(minutes=6) dupeList = (row for row in featList if (row[3] == sat and row[2] > timeStart and row[2] < timeStop and row[0] <> featID)) for row in dupeList: if row[4] > nbMes: featIDs.append(featID) # mark the original for deletion skipID.append(row[0]) logger.append([ featID, tag, timeVal.strftime('%Y-%m-%d %H:%M:%S'), sat, nbMes ]) elif row[4] < nbMes: featIDs.append(row[0]) # mark the match for deletion skipID.append(row[0]) logger.append([ row[0], row[1], row[2].strftime('%Y-%m-%d %H:%M:%S'), row[2], row[3] ]) elif row[4] == nbMes: if row[2] < timeVal: featIDs.append(row[0]) # mark the match for deletion skipID.append(row[0]) logger.append([ row[0], row[1], row[2].strftime('%Y-%m-%d %H:%M:%S'), row[3], row[4] ]) else: featIDs.append( featID) # mark the original for deletion skipID.append(row[0]) logger.append([ featID, tag, timeVal.strftime('%Y-%m-%d %H:%M:%S'), sat, nbMes ]) print ptt + '...Done' #write deletions to log with open(outFile, 'ab') as outputFile: writer = csv.writer(outputFile) writer.writerow( ["FeatureID", "TagName", "TimeValue", "Sat.", "NbMsgs"]) for f in logger: writer.writerow(f) return featIDs