Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Table Data Model
#1
# 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()
Reply
#2
  • Sorry I didn't include any description:

  • This is a data model for any table view which will buffer any relational database table. I will be including more relational features as I work on it.

  • I am currently working on making it pluggable into any table view by including a listener registry which invokes the method registered by the view for callback whenever the selected change type occurs. With this a table component / widget can listen in real time for changes in the table data in order to update the view. I have a table view that I obtained at ActiveState that I will use to test this feature. This view-model component integration will be my next post here.

  • The next step will be to add a listener registry to the table view component for editing that its user might want to do to the table data. This allows the table view to work much like a spreadsheet, and in will be including math libraries and computed fields / cells in the design.

  • I will also be writing an automatic dialog and property sheet generator for single record viewing and editing, with the ability to step through the records in a table.

  • Filtering and sorting will be included in my enhancements to the table view I obtained, as well as the ability to design a DB table and enter data into it. Adding a field will involve only specifying the field meta-data, and the user will be able to add fields / columns after row data has been entered, allowing a very flexible design workflow.

  • As you've probably guessed, this will be a database design and navigation front end. I'm planning on supporting only SQLite for now, and will be integrating this with federal online data sources such as those in Socrata and the Energy Information Administration. The database(s0 will be a buffer for the user's selection of tables from these sources (to prevent repetitive requests), and i will be integrating matplotlib for analytics along with, as I said, as many of the standard relational functions as I can pack in.
Reply


Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020