# Class: TableData # Date Started: October 22, 2017 # Author: George Keith Watson, a.k.a. Keith Michael Collins # Copyrights: (c) 2017 George Keith Watson, a.k.a. Keith Michael Collins # Documentation: # # 2017-10-24 To Do: # QA current code # Add field maps for semantic relationships between tables, e.g. where any record can have detail records in # another table, forming a hierarchy. Structure is: # ( TableOne.FieldValue, TableOne,RecordIndex, TableTwo.FieldValue, TableTwo,RecordIndex ) # where the detail table is TableTwo. # For editable / updatable fields, records and recordSets / Tables, an updater function must be attachable to # any individual field value, field column, record, or table. The hierarchical dependency structure should # be homologous with the detailing structure created by the field maps, but doesn't have to be. # from datetime import date class TableData: def __init__(self, dataTable, selectedFields=None): self.dataTable = dataTable testData = OrderHistory.data # Sorted binary trees for any field self.indexTrees = {} # Sorted lists of ( fieldValue, rowIndex ) tupples for those field indexTrees that have been traversed self.indexes = {} # Result sets caching. Multiple level key using field name, comparison operator, and value compared self.resultSets = {} # Index included fields if selectedFields != None: # SELECTED ONLY for field in dataTable["fields"]: for name in selectedFields: if field["name"] == name: self.buildIndex(name) self.traverseIndex(name,self.indexTrees[field["name"]]["root"]) else: for field in dataTable["fields"]: self.buildIndex(field["name"]) # Produce linear indexes self.traverseIndex(field["name"],self.indexTrees[field["name"]]["root"]) def getFieldsMeta(self): return self.dataTable["fields"] def getHeadings(self): return self.dataTable["headings"] def getDataRows(self): return self.dataTable["rows"] def getField(self, fieldName): self.columnIndex = 0; for fieldDescriptor in self.dataTable["fields"]: if fieldDescriptor["name"] == fieldName: return fieldDescriptor return None def getColumn(self, fieldName): fieldDescriptor = self.getField( fieldName) if fieldDescriptor == None: return None self.rowIndex = 0 self.columnIndex = fieldDescriptor["rowOffset"] column = [] typeDescriptor = None while self.rowIndex < len( self.dataTable["rows"] ): if self.rowIndex == 0: typeDescriptor = fieldDescriptor["type"].split( "." ) fieldValue = self.dataTable["rows"][self.rowIndex][self.columnIndex] if typeDescriptor[0].lower() == "string": if len ( typeDescriptor) > 1: if typeDescriptor[1].lower() == "date": if len(typeDescriptor) > 2: if typeDescriptor[2].lower() == "us": dateParts = fieldValue.split(typeDescriptor[3]) fieldValue = str(date(int(dateParts[2]),int(dateParts[0]),int(dateParts[1]))) else: pass else: pass else: pass else: pass else: pass column.append( fieldValue ) self.rowIndex += 1 return column class IndexNode: def __init__(self, value ): # value is a tupple containing at 0 the field value and a1 1 its rowIndex in the table self.value = value self.right = None self.left = None def addIndexEntry(self, fieldName, treeNode ): currentNode = self.indexTrees[fieldName]["root"] # Will determination of type ever be needed if the value is guaranteed to be in a comparable form? placed = False while not placed and currentNode != None: if treeNode.value[0] >= currentNode.value[0]: if currentNode.right == None: currentNode.right = treeNode placed = True else: currentNode = currentNode.right else: if currentNode.left == None: currentNode.left = treeNode placed = True else: currentNode = currentNode.left return placed def buildIndex(self, fieldName): fieldDescriptor = self.getField( fieldName) if fieldDescriptor == None: self.indexTrees[fieldName] = None self.indexes[fieldName] = None return else: fieldValues = self.getColumn(fieldName) self.indexTrees[fieldName] = {} self.indexes[fieldName] = {} self.indexTrees[fieldName]["fieldDescriptor"] = fieldDescriptor self.indexes[fieldName]["fieldDescriptor"] = fieldDescriptor if len( fieldValues ) == 0: self.indexTrees[fieldName]["root"] = None self.indexes[fieldName]["index"] = None return rowIndex = 0 self.indexTrees[fieldName]["root"] = TableData.IndexNode((fieldValues[rowIndex], rowIndex)) self.indexes[fieldName]["index"] = [] rowIndex += 1 while rowIndex < len(fieldValues): self.addIndexEntry( fieldName, TableData.IndexNode( ( fieldValues[rowIndex], rowIndex ) ) ) rowIndex += 1 # Traverse a particular field's index and produce a list of (field value, rowIndex) tupples in sorted order def traverseIndex(self, fieldName, currentNode ): if currentNode.left != None: self.traverseIndex( fieldName, currentNode.left ) self.indexes[fieldName]["index"].append( currentNode.value ) if currentNode.right != None: self.traverseIndex(fieldName, currentNode.right) # This assumes unique values for a field and returns the rowIndex of the first match. # Should return a tupple of rowIndexes including every match. def getRowIndex( self, fieldName, value ): if self.indexTrees[fieldName] == None or self.indexTrees[fieldName] == {}: return None currentNode = self.indexTrees[fieldName]["root"] while currentNode != None: if currentNode.value[0] == value: return currentNode.value[1] elif currentNode.value[0] < value: currentNode = currentNode.left elif currentNode.value[0] > value: currentNode = currentNode.right return None def selectFields( self, selectFieldLocations, rowData ): selectedValues = [] for location in selectFieldLocations: selectedValues.append( rowData[location] ) return selectedValues # return all rows based on single field comparison: value comparisonOperator fieldValue def getRowsSimple( self, comparisonTupple, **keyWordArguments ): # keyWordArguments["selectFields"] is a tupple listing the names of the fields to include in the resultSet if comparisonTupple != None: fieldName = comparisonTupple[0] comparisonOperator = comparisonTupple[1] value = comparisonTupple[2] selectingFields = False selectFieldLocations = None if "selectFields" in keyWordArguments: if keyWordArguments["selectFields"] != None: selectingFields = True # retrieve the location of each field in a row selectFieldLocations = [] for name in keyWordArguments["selectFields"]: fieldDefinition = self.getField(name) selectFieldLocations.append( fieldDefinition["rowOffset"] ) resultSetId = (comparisonTupple,keyWordArguments["selectFields"]) else: resultSetId = comparisonTupple else: resultSetId = comparisonTupple rowSet = [] if self.indexes[fieldName] == None or self.indexes[fieldName]["index"] == None: return rowSet if resultSetId in self.resultSets: if "table" in keyWordArguments: if keyWordArguments["table"].lower() in ["true", "yes"]: resultSetTable = {} resultSetTable["fields"] = list(self.dataTable["fields"]) resultSetTable["headings"] = list(self.dataTable["headings"]) if selectingFields: resultSetTable["rows"] = [] for rowData in self.resultSets[resultSetId]: resultSetTable["rows"].append( self.selectFields( selectFieldLocations, rowData ) ) return TableData(resultSetTable, keyWordArguments["selectFields"]) else: resultSetTable["rows"] = self.resultSets[resultSetId] return TableData(resultSetTable) resultSetRows = [] if selectingFields: for rowData in self.resultSets[resultSetId]: resultSetRows.append(self.selectFields(selectFieldLocations, rowData)) else: resultSetRows = self.resultSets[resultSetId] return resultSetRows index = 0 while index < len( self.indexes[fieldName]["index"] ): # get qualified record using row index of original table self.currentRow = self.dataTable["rows"][self.indexes[fieldName]["index"][index][1]] if comparisonOperator == "==": if self.indexes[fieldName]["index"][index][0] == value: if selectingFields: rowSet.append(self.selectFields( selectFieldLocations, self.currentRow )) else: rowSet.append(list(self.currentRow)) elif comparisonOperator == "!=": if self.indexes[fieldName]["index"][index][0] != value: if selectingFields: rowSet.append(self.selectFields( selectFieldLocations, self.currentRow )) else: rowSet.append(list(self.currentRow)) elif comparisonOperator == ">=": if self.indexes[fieldName]["index"][index][0] >= value: if selectingFields: rowSet.append(self.selectFields( selectFieldLocations, self.currentRow )) else: rowSet.append(list(self.currentRow)) elif comparisonOperator == "<=": if self.indexes[fieldName]["index"][index][0] <= value: if selectingFields: rowSet.append(self.selectFields( selectFieldLocations, self.currentRow )) else: rowSet.append(list(self.currentRow)) elif comparisonOperator == ">": if self.indexes[fieldName]["index"][index][0] > value: if selectingFields: rowSet.append(self.selectFields( selectFieldLocations, self.currentRow )) else: rowSet.append(list(self.currentRow)) elif comparisonOperator == "<": if self.indexes[fieldName]["index"][index][0] < value: if selectingFields: rowSet.append(self.selectFields( selectFieldLocations, self.currentRow )) else: rowSet.append(list(self.currentRow)) index += 1 self.resultSets[resultSetId] = rowSet if "table" in keyWordArguments: if keyWordArguments["table"].lower() in ["true","yes"]: resultSetTable = {} resultSetTable["fields"] = list(self.dataTable["fields"]) resultSetTable["headings"] = list(self.dataTable["headings"]) resultSetTable["rows"] = list(rowSet) if selectFieldLocations == None: return TableData(resultSetTable) else: return TableData(resultSetTable, keyWordArguments["selectFields"]) return rowSet # return all rows based on complex key, multiple field comparison: values comparisonOperator fieldValues # Rule: first key's records are found, then within that set the second, then third .... # comparisonDescriptorTuppleList is a list of tuples: ( fieldName, comparisonOperator, value ) def getRowsComplex(self, comparisonDescriptorTuppleList, **keyWordArguments ): if "table" in keyWordArguments: finalResultIsTable = keyWordArguments["table"] else: finalResultIsTable = False if "selectFields" in keyWordArguments: selectFields = keyWordArguments["selectFields"] else: selectFields = None keyWordArguments["selectFields"] = None keyWordArguments["table"] = "true" resultTable = self.getRowsSimple(comparisonDescriptorTuppleList[0], **keyWordArguments) tuppleIndex = 1 while tuppleIndex < len( comparisonDescriptorTuppleList ): resultTable = resultTable.getRowsSimple( comparisonDescriptorTuppleList[tuppleIndex], **keyWordArguments ) tuppleIndex += 1 if selectFields != None: selectFieldLocations = [] for name in selectFields: fieldDefinition = self.getField(name) selectFieldLocations.append(fieldDefinition["rowOffset"]) rowIndex = 0 while rowIndex < len(resultTable.dataTable["rows"]): resultTable.dataTable["rows"][rowIndex] = self.selectFields( selectFieldLocations, resultTable.dataTable["rows"][rowIndex]) rowIndex += 1 if finalResultIsTable: return resultTable else: return resultTable.dataTable["rows"] @staticmethod def printRowSet( rowSet ): print() for row in rowSet: print( str( row )) def printData(self): print() for row in self.dataTable["rows"]: print( str( row )) # TEST DATA class OrderHistory: # Copied from: http://www.contextures.com/xlSampleData01.html data = {"fields": [{"name": "OrderDate", "key": "true", "index": "true", "type": "string.date.us./", "units": "calendar", "rowOffset": 0, "editable": "no" }, {"name": "Region", "key": "false", "index": "true", "type": "string", "units": "__na__", "rowOffset": 1 }, {"name": "Rep", "key": "false", "index": "true", "type": "string.name", "units": "__na__", "rowOffset": 2 }, {"name": "Item", "key": "false", "index": "true", "type": "string.productName", "units": "__na__", "rowOffset": 3 }, {"name": "Units", "key": "false", "index": "true", "type": "integer", "units": "units", "rowOffset": 4 }, {"name": "UnitCost", "key": "false", "index": "true", "type": "float.currency", "units": "dollars", "rowOffset": 5 }, {"name": "Total", "key": "false", "index": "true", "type": "float.currenty", "units": "dollars", "rowOffset": 6 } ], "headings": ["OrderDate", "Region", "Rep", "Item", "Units", "UnitCost", "Total"], "rows": [["01/06/2016", "East", "Jones", "Pencil", 95, 1.99, 189.05], ["01/23/2016", "Central", "Kivell", "Binder", 50, 19.99, 999.50], ["02/09/2016", "Central", "Jardine", "Pencil", 36, 4.99, 179.64], ["2/26/2016", "Central", "Gill", "Pen", 27, 19.99, 539.73], ["3/15/2016", "West", "Sorvino", "Pencil", 56, 2.99, 167.44], ["4/1/2016", "East", "Jones", "Binder", 60, 4.99, 299.40], ["4/18/2016", "Central", "Andrews", "Pencil", 75, 1.99, 149.25], ["5/5/2016", "Central", "Jardine", "Pencil", 90, 4.99, 449.10], ["5/22/2016", "West", "Thompson", "Pencil", 32, 1.99, 63.68], ["6/8/2016", "East", "Jones", "Binder", 60, 8.99, 539.40], ["6/25/2016", "Central", "Morgan", "Pencil", 90, 4.99, 449.10], ["7/12/2016", "East", "Howard", "Binder", 29, 1.99, 57.71], ["7/29/2016", "East", "Parent", "Binder", 81, 19.99, 619.19], ["8/15/2016", "East", "Jones", "Pencil", 35, 4.99, 174.65], ["9/1/2016", "Central", "Smith", "Desk", 2, 125.00, 250.00], ["9/18/2016", "East", "Jones", "Pen Set", 16, 15.99, 255.84], ["10/5/2016", "Central", "Morgan", "Binder", 28, 8.99, 251.72], ["10/22/2016", "East", "Jones", "Pen", 64, 8.99, 575.36], ["11/8/2016", "East", "Parent", "Pen", 15, 19.99, 299.85], ["11/25/2016", "Central", "Kivell", "Pen Set", 96, 4.99, 479.04], ["12/12/2016", "Central", "Smith", "Pencil", 67, 1.29, 86.43], ["12/29/2016", "East", "Parent", "Pen Set", 74, 15.99, 183.26], ["1/15/2017", "Central", "Gill", "Binder", 46, 8.99, 413.54], ["2/1/2017", "Central", "Smith", "Binder", 87, 15.00, 305.00], ["2/18/2017", "East", "Jones", "Binder", 4, 4.99, 19.96], ["3/7/2017", "West", "Sorvino", "Binder", 7, 19.99, 139.93], ["3/24/2017", "Central", "Jardine", "Pen Set", 50, 4.99, 249.50], ["4/10/2017", "Central", "Andrews", "Pencil", 66, 1.99, 131.34], ["4/27/2017", "East", "Howard", "Pen", 96, 4.99, 479.04], ["5/14/2017", "Central", "Gill", "Pencil", 53, 1.29, 68.37], ["5/31/2017", "Central", "Gill", "Binder", 80, 8.99, 719.20], ["6/17/2017", "Central", "Kivell", "Desk", 5, 125.00, 625.00], ["7/4/2017", "East", "Jones", "Pen Set", 62, 4.99, 309.38], ["7/21/2017", "Central", "Morgan", "Pen Set", 55, 12.49, 686.95], ["8/7/2017", "Central", "Kivell", "Pen Set", 42, 23.95, 005.90], ["8/24/2017", "West", "Sorvino", "Desk", 3, 275.00, 825.00], ["9/10/2017", "Central", "Gill", "Pencil", 7, 1.29, 9.03], ["9/27/2017", "West", "Sorvino", "Pen", 76, 1.99, 151.24], ["10/14/2017", "West", "Thompson", "Binder", 57, 19.99, 139.43], ["10/31/2017", "Central", "Andrews", "Pencil", 14, 1.29, 18.06], ["11/17/2017", "Central", "Jardine", "Binder", 11, 4.99, 54.89], ["12/4/2017", "Central", "Jardine", "Binder", 94, 19.99, 879.06], ["12/21/2017", "Central", "Andrews", "Binder", 28, 4.99, 139.72] ] } def __init__(self): pass if __name__ == "__main__": tableData = TableData(OrderHistory.data) resultSetTable = tableData.getRowsSimple(("Total", "<", 100), table="yes", selectFields = ("OrderDate", "Region", "Rep", "Item", "Units", "UnitCost")) resultSetTable.printData() resultSetTable = tableData.getRowsComplex((("Total", ">", 100), ("Rep", "==", "Kivell")), table="yes", selectFields = ("OrderDate", "Rep", "Item", "Total")) resultSetTable.printData()
Table Data Model
Users browsing this thread: 3 Guest(s)