def update_houseids(self, hhldSyn, persSyn, hhldVars, persVars, highestHid): hhldSynDataObj = DataArray(hhldSyn, hhldVars) persSynDataObj = DataArray(persSyn, persVars) maxFreqCol = amax(hhldSynDataObj.columns(['frequency']).data) powFreqCol = floor(log(maxFreqCol, 10)) + 1 coefficients = {'frequency': 1, 'hhid': 10**powFreqCol} newHid = hhldSynDataObj.calculate_equation(coefficients) hhldSynDataObj.setcolumn('hhid', newHid) newHid = persSynDataObj.calculate_equation(coefficients) persSynDataObj.setcolumn('hhid', newHid) hhldSynDataObj.sort([self.idSpec.hidName]) persSynDataObj.sort([self.idSpec.hidName, self.idSpec.pidName]) hidIndex_popgenH = hhldVars.index('hhid') hidIndex_popgenP = persVars.index('hhid') self.create_indices(persSynDataObj) hhldSyn = hhldSynDataObj.data persSyn = persSynDataObj.data row = 0 for hhldIndex in self.hhldIndicesOfPersons: firstPersonRec = hhldIndex[1] lastPersonRec = hhldIndex[2] #print hhldIndex[0], highestHid + 1, firstPersonRec, lastPersonRec hhldSyn[row, hidIndex_popgenH] = highestHid + 1 persSyn[firstPersonRec:lastPersonRec, hidIndex_popgenP] = highestHid + 1 highestHid += 1 row += 1 return hhldSyn, persSyn
def update_houseids(self, hhldSyn, persSyn, hhldVars, persVars, highestHid): hhldSynDataObj = DataArray(hhldSyn, hhldVars) persSynDataObj = DataArray(persSyn, persVars) maxFreqCol = amax(hhldSynDataObj.columns(['frequency']).data) powFreqCol = floor(log(maxFreqCol, 10)) + 1 coefficients = {'frequency':1, 'hhid':10**powFreqCol} newHid = hhldSynDataObj.calculate_equation(coefficients) hhldSynDataObj.setcolumn('hhid', newHid) newHid = persSynDataObj.calculate_equation(coefficients) persSynDataObj.setcolumn('hhid', newHid) hhldSynDataObj.sort([self.idSpec.hidName]) persSynDataObj.sort([self.idSpec.hidName, self.idSpec.pidName]) hidIndex_popgenH = hhldVars.index('hhid') hidIndex_popgenP = persVars.index('hhid') self.create_indices(persSynDataObj) hhldSyn = hhldSynDataObj.data persSyn = persSynDataObj.data row = 0 for hhldIndex in self.hhldIndicesOfPersons: firstPersonRec = hhldIndex[1] lastPersonRec = hhldIndex[2] #print hhldIndex[0], highestHid + 1, firstPersonRec, lastPersonRec hhldSyn[row,hidIndex_popgenH] = highestHid + 1 persSyn[firstPersonRec:lastPersonRec,hidIndex_popgenP] = highestHid + 1 highestHid += 1 row += 1 return hhldSyn, persSyn
def select_join(self, db_dict, column_names, table_names, max_dict=None, spatialConst_list=None, analysisInterval=None, subsample=None): """ self, table1_list, table2_list, column_name This method is used to select the join of tables and display them. Input: Database configuration object, table names, columns and values. DB_DICT {'households': ['htaz', 'numchild', 'inclt35k', 'hhsize'], 'persons': ['male', 'schstatus', 'one', 'houseid', 'personid'], 'schedule_r': ['scheduleid', 'activitytype']} COLUMN_NAMES {'households': ['houseid'], 'schedule_r': ['personid', 'houseid']} TABLE_NAMES ['persons', 'households', 'schedule_r'] MAX_DICT {'schedule_r': ['scheduleid']} SPATIALCONST_LIST [Table - travel_skims; Field - tt Orifin Field - origin; Destination Field - destination; Sample Field - destination Start Constraint - Table - schedule_r; Location Field - locationid; Time Field - endtime End Constraint - Table - schedule_r; Location Field - locationid; Time Field - starttime Number of choices - 5 Threshold for window - 120] ANALYSISINTERVAL 195 SUBSAMPLE None Test SQL String for above inputs: select households.htaz,households.numchild,households.inclt35k,households.hhsize,persons.male,persons.schstatus,persons.one, persons.houseid,persons.personid,schedule_r.activitytype,temp.scheduleid,stl.locationid as st_locationid,enl.locationid as en_locationid,sptime.st_endtime,sptime.en_starttime from persons left join households on ( persons.houseid=households.houseid ) left join (select personid,houseid, max(scheduleid) as scheduleid from schedule_r group by personid,houseid) as temp on ( temp.personid=persons.personid and temp.houseid=persons.houseid ) left join schedule_r on ( persons.personid=schedule_r.personid and persons.houseid=schedule_r.houseid and schedule_r.scheduleid=temp.scheduleid) join (select st.personid personid,st.houseid houseid, min(st.endtime) st_endtime,min(en.starttime) en_starttime from schedule_r as st inner join schedule_r as en on ( st.personid = en.personid and st.houseid = en.houseid and st.endtime=195 and en.starttime>195) group by st.personid,st.houseid) as sptime on ( sptime.personid = persons.personid and sptime.houseid = persons.houseid ) left join schedule_r stl on ( stl.personid = persons.personid and stl.houseid = persons.houseid and sptime.st_endtime = stl.endtime) left join schedule_r enl on ( enl.personid = persons.personid and enl.houseid = persons.houseid and sptime.en_starttime = enl.starttime) Output: Displays the rows based on the join and the selection criterion. """ self.dbcon_obj.new_sessionInstance() print 'DB_DICT', db_dict print 'COLUMN_NAMES', column_names print 'TABLE_NAMES', table_names print 'MAX_DICT', max_dict print 'SPATIALCONST_LIST', spatialConst_list print 'ANALYSISINTERVAL', analysisInterval print 'SUBSAMPLE', subsample #db_dict = {'households': ['urb', 'numchild', 'inclt35k', 'ownhome', 'one', 'drvrcnt', 'houseid'], # 'vehicles_r': ['vehtype', 'vehid'], # 'households_r': ['numvehs']} #columns_names = ['houseid'] #table_names = ['households', 'households_r', 'vehicles_r'] #max_dict = {'vehicles_r':['vehid']} # Prism Query or or just a Travel Time Query with Vertices # ADD APPROPRIATE JOIN/?INNER JOINS print 'Database Dictionary of Table and Columns - ', db_dict print 'Column Names', column_names #raw_input() #initialize the variables final_list = [] table_list = [] class_list = [] cols_list = [] tabs_list = [] #col_name = column_name final_col_list = db_dict.values() table_list = db_dict.keys() """ #check if the table exists. If not return none if chk_table.lower() in [each.lower() for each in table_list]: print 'table %s is present in the table list'%chk_table else: print 'table %s is not present in the table list'%chk_table return None """ #similarly check if the table in the list exists num_tab = len(list(set(table_list) & set(table_names))) if num_tab <= len(table_list): #print 'Tables exist' pass else: #print 'Tables do not exists' return None #check for the columns passed in the dictionary for i in db_dict.keys(): clist = self.dbcon_obj.get_column_list(i.lower()) list1 = db_dict[i] #print 'table--', i #print 'clist', clist #print 'list1', list1 chk_list = len(list(set(list1) & set(clist))) if chk_list == len(list1): for j in db_dict[i]: #print '\tColumn - ', j new_str = i.lower() + '.' + j.lower() final_list.append(new_str) else: print ('Column passed in the dictionary does not exist in the table - ') print 'Column List in the Table - ', clist print 'Actual List of Columns requested from table - ', list1 return None #print 'final_list is %s'%final_list #print 'FINAL LIST', final_list #print 'TABLE LIST', table_list # Generating the left join statements mainTable = table_names[0] #print 'mainTable ----> ', mainTable primCols = [] for i in column_names: primCols += column_names[i] primCols = list(set(primCols)) joinStrList = [] for table in table_list: if table == mainTable: continue joinCondition = '' for col in column_names[table]: joinCondition = (joinCondition + ' %s.%s=%s.%s ' %(mainTable, col, table, col) + 'and') joinCondition = joinCondition[:-3] joinStr = ' left join %s on (%s)' %(table, joinCondition) joinStrList.append(joinStr) #print 'JOIN STRING LIST', joinStrList #check the max flag if max_dict is not None: max_flag = 1 max_table = max_dict.keys() max_column = max_dict.values()[0][0] #print max_column, max_table #for each in max_dict.values(): # max_column = each[0] else: max_flag = 0 # Index of the table containing the max dict var # as it stands only querying for one count variable is # provided #print max_dict if max_dict is not None: maxTable = max_dict.keys()[0] maxColumn = max_dict.values()[0][0] index = table_list.index(maxTable) #print 'INDEX--->', index #remove the count column from the col list countVarStr = '%s.%s' %(maxTable, maxColumn) final_list.remove(countVarStr) final_list.append('temp.%s'%maxColumn) #print 'NEW FINAL LIST -->', final_list # left join for the count variable joinStr = '' #grouping string grpStr = '' joinCondition='' #print 'column_names of max TABLE ----->', column_names for i in column_names[maxTable]: #print 'createing join string for column name - ', i grpStr = grpStr + '%s,' %(i) joinCondition = (joinCondition + ' temp.%s=%s.%s ' %(i, mainTable, i) + 'and') grpStr = grpStr[:-1] joinCondition = joinCondition[:-3] #combine left join along with the count variable/max condition mJoinStr = joinStrList.pop(index-1) mJoinStrIncMaxConditionVar = (mJoinStr[:-1] + 'and %s.%s=temp.%s)' %(maxTable, maxColumn, maxColumn)) joinStrList.append(""" left join (select %s, max(%s) as %s from """ """%s group by %s) as temp on (%s) """ %(grpStr, maxColumn, maxColumn,maxTable, grpStr, joinCondition) + mJoinStrIncMaxConditionVar) #print 'LEFT JOIN MAX COL LIST--->', joinStrList # Spatial TSP identification if spatialConst_list is not None: for i in spatialConst_list: if i.countChoices is not None: # substring for the inner join stTable = i.startConstraint.table #stLocationField = 'st_' + i.startConstraint.locationField stLocationCol = 'stl.%s' %i.startConstraint.locationField #stTimeField = 'st_'+ i.startConstraint.timeField stTimeCol = 'st.%s' %i.startConstraint.timeField enTable = i.endConstraint.table #enLocationField = 'en_' + i.endConstraint.locationField enLocationCol = 'enl.%s' %i.endConstraint.locationField #enTimeField = 'en_' + i.endConstraint.timeField enTimeCol = 'en.%s' %i.endConstraint.timeField timeCols = [stTimeCol, enTimeCol] table_list.append(stTable) # left join for end location # time cols are part of sptime timeColsNewNames = [] for j in timeCols: timeColsNewNames.append(j.replace('.', '_')) timeColsStr = '' for j in range(len(timeCols)): # minimum of the time cols gives the first prism timeColsStr += 'min(%s) %s,' %(timeCols[j], timeColsNewNames[j]) timeColsStr = timeColsStr[:-1] spGrpNewNameStr = '' spGrpStr = '' for j in column_names[stTable]: spGrpNewNameStr += 'st.%s %s,' %(j, j) spGrpStr += 'st.%s,' %(j) spGrpNewNameStr = spGrpNewNameStr[:-1] spGrpStr = spGrpStr[:-1] spInnerJoinCondition = '' for j in column_names[stTable]: spInnerJoinCondition += ' %s.%s = %s.%s and' %('st', j, 'en', j) spInnerJoinCondition = spInnerJoinCondition[:-3] spJoinCondition = '' for j in column_names[stTable]: spJoinCondition += ' %s.%s = %s.%s and' %('sptime', j, mainTable, j) spJoinCondition = spJoinCondition[:-3] # Left join condition for prism start location stLocJoinCondition = '' stLocCondCols = column_names[stTable] for j in stLocCondCols: stLocJoinCondition += ' %s.%s = %s.%s and' %('stl', j, mainTable, j) stLocJoinCondition += ' sptime.st_%s = %s.%s' %(i.startConstraint.timeField, 'stl', i.startConstraint.timeField) final_list.append('stl.%s as st_%s' %(i.startConstraint.locationField, i.startConstraint.locationField)) cols_list.append('st_%s' %i.startConstraint.locationField) #stLocJoinCondition = stLocJoinCondition[:-3] # Left join condition for prism end location enLocJoinCondition = '' enLocCondCols = column_names[stTable] for j in enLocCondCols: enLocJoinCondition += ' %s.%s = %s.%s and' %('enl', j, mainTable, j) enLocJoinCondition += ' sptime.en_%s = %s.%s' %(i.endConstraint.timeField, 'enl', i.endConstraint.timeField) final_list.append('enl.%s as en_%s' %(i.endConstraint.locationField, i.endConstraint.locationField)) cols_list.append('en_%s' %i.endConstraint.locationField) #enLocJoinCondition = enLocJoinCondition[:-3] # TSP consistency check # nextepisode_starttime > lastepisode_endtime #consistencyStr = '%s < %s' %(stTimeCol, endTimeCol) analysisPeriodStr = ('%s=%s and %s>%s' %(stTimeCol, analysisInterval, enTimeCol, analysisInterval)) spatialJoinStr = (""" join (select %s, %s """\ """from %s as %s """\ """inner join %s as %s """\ """on ( %s and %s) group by"""\ """ %s) """\ """as sptime on (%s)""" % (spGrpNewNameStr, timeColsStr, stTable, 'st', enTable, 'en', spInnerJoinCondition, analysisPeriodStr, spGrpStr, spJoinCondition)) #print 'SPATIAL JOIN' #print spatialJoinStr # left join for start location stLocJoinStr = (""" left join %s %s on """\ """(%s) """ %(stTable, 'stl', stLocJoinCondition)) enLocJoinStr = (""" left join %s %s on """\ """(%s) """ %(enTable, 'enl', enLocJoinCondition)) joinStrList.append(spatialJoinStr) joinStrList.append(stLocJoinStr) joinStrList.append(enLocJoinStr) cols_list += timeColsNewNames for i in timeColsNewNames: final_list.append('sptime.%s' %(i)) # Only one time-space prism can be retrieved within a component # there cannot be two TSP's in the same component break # Generating the col list colStr = '' for i in final_list: colStr = colStr + '%s,' %(i) colStr = colStr[:-1] # Build the SQL string allJoinStr = '' for i in joinStrList: allJoinStr = allJoinStr + '%s' %i sql_string = 'select %s from %s %s' %(colStr, mainTable, allJoinStr) print 'SQL string for query - ', sql_string #convert all the table names to upper case for each in table_list: tabs_list.append(each.upper()) #print 'tabs_list is %s'%tabs_list #separate all the columns from the lists new_keys = db_dict.keys() for i in new_keys: cols_list = cols_list + db_dict[i] #print 'cols_list is %s'%cols_list try: sample_str = '' ctr = 0 for i in tabs_list: if ctr==0: sample_str = i ctr = ctr + 1 else: sample_str = sample_str + ', ' + i query = self.dbcon_obj.session.query((sample_str)) #print 'sample_str is %s'%sample_str result = query.from_statement(sql_string).values(*cols_list) resultArray = self.createResultArray(result) # Returns the query as a DataArray object data = DataArray(resultArray, cols_list) data.sort(primCols) self.dbcon_obj.close_sessionInstance() return data except Exception, e: print e print 'Error retrieving the information. Query failed.'
def select_join(self, db_dict, column_names, table_names, max_dict=None, spatialConst_list=None, analysisInterval=None, subsample=None): """ self, table1_list, table2_list, column_name This method is used to select the join of tables and display them. Input: Database configuration object, table names, columns and values. DB_DICT {'households': ['htaz', 'numchild', 'inclt35k', 'hhsize'], 'persons': ['male', 'schstatus', 'one', 'houseid', 'personid'], 'schedule_r': ['scheduleid', 'activitytype']} COLUMN_NAMES {'households': ['houseid'], 'schedule_r': ['personid', 'houseid']} TABLE_NAMES ['persons', 'households', 'schedule_r'] MAX_DICT {'schedule_r': ['scheduleid']} SPATIALCONST_LIST [Table - travel_skims; Field - tt Orifin Field - origin; Destination Field - destination; Sample Field - destination Start Constraint - Table - schedule_r; Location Field - locationid; Time Field - endtime End Constraint - Table - schedule_r; Location Field - locationid; Time Field - starttime Number of choices - 5 Threshold for window - 120] ANALYSISINTERVAL 195 SUBSAMPLE None Test SQL String for above inputs: select households.htaz,households.numchild,households.inclt35k,households.hhsize,persons.male,persons.schstatus,persons.one, persons.houseid,persons.personid,schedule_r.activitytype,temp.scheduleid,stl.locationid as st_locationid,enl.locationid as en_locationid,sptime.st_endtime,sptime.en_starttime from persons left join households on ( persons.houseid=households.houseid ) left join (select personid,houseid, max(scheduleid) as scheduleid from schedule_r group by personid,houseid) as temp on ( temp.personid=persons.personid and temp.houseid=persons.houseid ) left join schedule_r on ( persons.personid=schedule_r.personid and persons.houseid=schedule_r.houseid and schedule_r.scheduleid=temp.scheduleid) join (select st.personid personid,st.houseid houseid, min(st.endtime) st_endtime,min(en.starttime) en_starttime from schedule_r as st inner join schedule_r as en on ( st.personid = en.personid and st.houseid = en.houseid and st.endtime=195 and en.starttime>195) group by st.personid,st.houseid) as sptime on ( sptime.personid = persons.personid and sptime.houseid = persons.houseid ) left join schedule_r stl on ( stl.personid = persons.personid and stl.houseid = persons.houseid and sptime.st_endtime = stl.endtime) left join schedule_r enl on ( enl.personid = persons.personid and enl.houseid = persons.houseid and sptime.en_starttime = enl.starttime) Output: Displays the rows based on the join and the selection criterion. """ self.dbcon_obj.new_sessionInstance() print 'DB_DICT', db_dict print 'COLUMN_NAMES', column_names print 'TABLE_NAMES', table_names print 'MAX_DICT', max_dict print 'SPATIALCONST_LIST', spatialConst_list print 'ANALYSISINTERVAL', analysisInterval print 'SUBSAMPLE', subsample #db_dict = {'households': ['urb', 'numchild', 'inclt35k', 'ownhome', 'one', 'drvrcnt', 'houseid'], # 'vehicles_r': ['vehtype', 'vehid'], # 'households_r': ['numvehs']} #columns_names = ['houseid'] #table_names = ['households', 'households_r', 'vehicles_r'] #max_dict = {'vehicles_r':['vehid']} # Prism Query or or just a Travel Time Query with Vertices # ADD APPROPRIATE JOIN/?INNER JOINS print 'Database Dictionary of Table and Columns - ', db_dict print 'Column Names', column_names #raw_input() #initialize the variables final_list = [] table_list = [] class_list = [] cols_list = [] tabs_list = [] #col_name = column_name final_col_list = db_dict.values() table_list = db_dict.keys() """ #check if the table exists. If not return none if chk_table.lower() in [each.lower() for each in table_list]: print 'table %s is present in the table list'%chk_table else: print 'table %s is not present in the table list'%chk_table return None """ #similarly check if the table in the list exists num_tab = len(list(set(table_list) & set(table_names))) if num_tab <= len(table_list): #print 'Tables exist' pass else: #print 'Tables do not exists' return None #check for the columns passed in the dictionary for i in db_dict.keys(): clist = self.dbcon_obj.get_column_list(i.lower()) list1 = db_dict[i] #print 'table--', i #print 'clist', clist #print 'list1', list1 chk_list = len(list(set(list1) & set(clist))) if chk_list == len(list1): for j in db_dict[i]: #print '\tColumn - ', j new_str = i.lower() + '.' + j.lower() final_list.append(new_str) else: print( 'Column passed in the dictionary does not exist in the table - ' ) print 'Column List in the Table - ', clist print 'Actual List of Columns requested from table - ', list1 return None #print 'final_list is %s'%final_list #print 'FINAL LIST', final_list #print 'TABLE LIST', table_list # Generating the left join statements mainTable = table_names[0] #print 'mainTable ----> ', mainTable primCols = [] for i in column_names: primCols += column_names[i] primCols = list(set(primCols)) joinStrList = [] for table in table_list: if table == mainTable: continue joinCondition = '' for col in column_names[table]: joinCondition = (joinCondition + ' %s.%s=%s.%s ' % (mainTable, col, table, col) + 'and') joinCondition = joinCondition[:-3] joinStr = ' left join %s on (%s)' % (table, joinCondition) joinStrList.append(joinStr) #print 'JOIN STRING LIST', joinStrList #check the max flag if max_dict is not None: max_flag = 1 max_table = max_dict.keys() max_column = max_dict.values()[0][0] #print max_column, max_table #for each in max_dict.values(): # max_column = each[0] else: max_flag = 0 # Index of the table containing the max dict var # as it stands only querying for one count variable is # provided #print max_dict if max_dict is not None: maxTable = max_dict.keys()[0] maxColumn = max_dict.values()[0][0] index = table_list.index(maxTable) #print 'INDEX--->', index #remove the count column from the col list countVarStr = '%s.%s' % (maxTable, maxColumn) final_list.remove(countVarStr) final_list.append('temp.%s' % maxColumn) #print 'NEW FINAL LIST -->', final_list # left join for the count variable joinStr = '' #grouping string grpStr = '' joinCondition = '' #print 'column_names of max TABLE ----->', column_names for i in column_names[maxTable]: #print 'createing join string for column name - ', i grpStr = grpStr + '%s,' % (i) joinCondition = (joinCondition + ' temp.%s=%s.%s ' % (i, mainTable, i) + 'and') grpStr = grpStr[:-1] joinCondition = joinCondition[:-3] #combine left join along with the count variable/max condition mJoinStr = joinStrList.pop(index - 1) mJoinStrIncMaxConditionVar = (mJoinStr[:-1] + 'and %s.%s=temp.%s)' % (maxTable, maxColumn, maxColumn)) joinStrList.append(""" left join (select %s, max(%s) as %s from """ """%s group by %s) as temp on (%s) """ % (grpStr, maxColumn, maxColumn, maxTable, grpStr, joinCondition) + mJoinStrIncMaxConditionVar) #print 'LEFT JOIN MAX COL LIST--->', joinStrList # Spatial TSP identification if spatialConst_list is not None: for i in spatialConst_list: if i.countChoices is not None: # substring for the inner join stTable = i.startConstraint.table #stLocationField = 'st_' + i.startConstraint.locationField stLocationCol = 'stl.%s' % i.startConstraint.locationField #stTimeField = 'st_'+ i.startConstraint.timeField stTimeCol = 'st.%s' % i.startConstraint.timeField enTable = i.endConstraint.table #enLocationField = 'en_' + i.endConstraint.locationField enLocationCol = 'enl.%s' % i.endConstraint.locationField #enTimeField = 'en_' + i.endConstraint.timeField enTimeCol = 'en.%s' % i.endConstraint.timeField timeCols = [stTimeCol, enTimeCol] table_list.append(stTable) # left join for end location # time cols are part of sptime timeColsNewNames = [] for j in timeCols: timeColsNewNames.append(j.replace('.', '_')) timeColsStr = '' for j in range(len(timeCols)): # minimum of the time cols gives the first prism timeColsStr += 'min(%s) %s,' % (timeCols[j], timeColsNewNames[j]) timeColsStr = timeColsStr[:-1] spGrpNewNameStr = '' spGrpStr = '' for j in column_names[stTable]: spGrpNewNameStr += 'st.%s %s,' % (j, j) spGrpStr += 'st.%s,' % (j) spGrpNewNameStr = spGrpNewNameStr[:-1] spGrpStr = spGrpStr[:-1] spInnerJoinCondition = '' for j in column_names[stTable]: spInnerJoinCondition += ' %s.%s = %s.%s and' % ( 'st', j, 'en', j) spInnerJoinCondition = spInnerJoinCondition[:-3] spJoinCondition = '' for j in column_names[stTable]: spJoinCondition += ' %s.%s = %s.%s and' % ( 'sptime', j, mainTable, j) spJoinCondition = spJoinCondition[:-3] # Left join condition for prism start location stLocJoinCondition = '' stLocCondCols = column_names[stTable] for j in stLocCondCols: stLocJoinCondition += ' %s.%s = %s.%s and' % ( 'stl', j, mainTable, j) stLocJoinCondition += ' sptime.st_%s = %s.%s' % ( i.startConstraint.timeField, 'stl', i.startConstraint.timeField) final_list.append('stl.%s as st_%s' % (i.startConstraint.locationField, i.startConstraint.locationField)) cols_list.append('st_%s' % i.startConstraint.locationField) #stLocJoinCondition = stLocJoinCondition[:-3] # Left join condition for prism end location enLocJoinCondition = '' enLocCondCols = column_names[stTable] for j in enLocCondCols: enLocJoinCondition += ' %s.%s = %s.%s and' % ( 'enl', j, mainTable, j) enLocJoinCondition += ' sptime.en_%s = %s.%s' % ( i.endConstraint.timeField, 'enl', i.endConstraint.timeField) final_list.append('enl.%s as en_%s' % (i.endConstraint.locationField, i.endConstraint.locationField)) cols_list.append('en_%s' % i.endConstraint.locationField) #enLocJoinCondition = enLocJoinCondition[:-3] # TSP consistency check # nextepisode_starttime > lastepisode_endtime #consistencyStr = '%s < %s' %(stTimeCol, endTimeCol) analysisPeriodStr = ('%s=%s and %s>%s' % (stTimeCol, analysisInterval, enTimeCol, analysisInterval)) spatialJoinStr = (""" join (select %s, %s """\ """from %s as %s """\ """inner join %s as %s """\ """on ( %s and %s) group by"""\ """ %s) """\ """as sptime on (%s)""" % (spGrpNewNameStr, timeColsStr, stTable, 'st', enTable, 'en', spInnerJoinCondition, analysisPeriodStr, spGrpStr, spJoinCondition)) #print 'SPATIAL JOIN' #print spatialJoinStr # left join for start location stLocJoinStr = (""" left join %s %s on """\ """(%s) """ %(stTable, 'stl', stLocJoinCondition)) enLocJoinStr = (""" left join %s %s on """\ """(%s) """ %(enTable, 'enl', enLocJoinCondition)) joinStrList.append(spatialJoinStr) joinStrList.append(stLocJoinStr) joinStrList.append(enLocJoinStr) cols_list += timeColsNewNames for i in timeColsNewNames: final_list.append('sptime.%s' % (i)) # Only one time-space prism can be retrieved within a component # there cannot be two TSP's in the same component break # Generating the col list colStr = '' for i in final_list: colStr = colStr + '%s,' % (i) colStr = colStr[:-1] # Build the SQL string allJoinStr = '' for i in joinStrList: allJoinStr = allJoinStr + '%s' % i sql_string = 'select %s from %s %s' % (colStr, mainTable, allJoinStr) print 'SQL string for query - ', sql_string #convert all the table names to upper case for each in table_list: tabs_list.append(each.upper()) #print 'tabs_list is %s'%tabs_list #separate all the columns from the lists new_keys = db_dict.keys() for i in new_keys: cols_list = cols_list + db_dict[i] #print 'cols_list is %s'%cols_list try: sample_str = '' ctr = 0 for i in tabs_list: if ctr == 0: sample_str = i ctr = ctr + 1 else: sample_str = sample_str + ', ' + i query = self.dbcon_obj.session.query((sample_str)) #print 'sample_str is %s'%sample_str result = query.from_statement(sql_string).values(*cols_list) resultArray = self.createResultArray(result) # Returns the query as a DataArray object data = DataArray(resultArray, cols_list) data.sort(primCols) self.dbcon_obj.close_sessionInstance() return data except Exception, e: print e print 'Error retrieving the information. Query failed.'