Append and Delete Rows? - wxPython

I am having trouble appending and deleting rows. My table changes a lot and must be rebuilt often so this has been a little tricky. All of my information comes from an SQL database. I am loading the results into a pandas DataFrame and then using it to populate the GridTableBase class. I am now trying to Append and Delete rows, but am having trouble overriding the class. I have been able to somewhat get it to work, but it behaves weird. For some reason, self.table.AppendRows(row)
doesn’t work and throws an error. The original was self.table.AppendRow(row)
, but AppendRow isn’t a method. So I had to use a different method. I have to change a value in order to get the GridTableMessage to realize there has been a change, which is what I am doing here data.iloc[data.shape[0]-1,0] = str(val)

Ideally, I would add/delete the row from the table itself, but I can’t figure out how to do that. I have derived most of my code from here Phoenix/Grid_MegaExample.py at 64e5d863f7833f10df6a0fbcf3221a730562224b · wxWidgets/Phoenix · GitHub but a lot of that will not work properly for me.

As of now, I can append a row, but for some reason, it appends 2 even though only one has been added to the DataFrame and GetNumberRows is returning the correct count. I assume it has something to do with the way I am accessing the table class. Can anyone provide some clarity?

Issue:

def rowPopup(self, row, evt):
    """(row, evt) -> display a popup menu when a row label is right clicked"""
    appendID = wx.Window.NewControlId()#wx.NewId()
    deleteID = wx.Window.NewControlId()#wx.NewId()
    x = self.GetRowSize(row)/2
    if not self.GetSelectedRows():
        self.SelectRow(row)
    menu = wx.Menu()
    xo, yo = evt.GetPosition()
    menu.Append(appendID, "Append Row")
    menu.Append(deleteID, "Delete Row(s)")

    def append(event, self=self, row=row):#event, self=self, row=row
        global data
        #print("Append")
        #self.table.AppendRows(row)
        dlg = wx.TextEntryDialog(self,'Enter a new Key ID to insert into the ' + str("'") + data.columns[0] + str("'") + ' column.', 'Insert New Record')
        dlg.SetValue("")
        if dlg.ShowModal() == wx.ID_OK:
        #print('You entered: %s\n' % dlg.GetValue())
            val = dlg.GetValue()
            #data[~pd.isnull(data).all(1)].fillna('')
            #data['tables_id'].apply('(g)'.format)
            data.loc[data.iloc[-1].name + 1,:] = ""
            data.iloc[data.shape[0]-1,0] = str(val)
            self.Reset()
            #print(data)
        #data = data.append(pd.Series(dtype='object'), ignore_index=True)
        #self.data = DataTable(data)
        #data[~pd.isnull(data).all(1)].fillna('')
        #self.data = DataTable(data)
        
    def delete(event, self=self, row=row):#event, self=self, row=row
        global data

        rows = self.GetSelectedRows()
        data.drop(data.index[rows],inplace=True)
        print (data)
        self.Reset()

        #self.table.DeleteRow(row)
        #print(row)
        #print(rows)
    
    #EVT_MENU(self, appendID, append)
    #EVT_MENU(self, deleteID, delete)
    self.Bind(wx.EVT_MENU, append, id=appendID)
    self.Bind(wx.EVT_MENU, delete, id=deleteID)
    self.PopupMenu(menu, wx.Point(round(x), round(yo)))
    menu.Destroy()

Code:

class DataTable(gridlib.GridTableBase):
   def __init__(self, data):

    gridlib.GridTableBase.__init__(self)
    self.headerRows = 1
    if data is None:
        data = pd.DataFrame()
    self.data = data
    print("Instance")
    #Store the row and col length to see if table has changed in size
    self._rows = self.GetNumberRows()
    self._cols = self.GetNumberCols()

    self.odd=gridlib.GridCellAttr()
    self.odd.SetBackgroundColour((217,217,217))
    self.even=gridlib.GridCellAttr()
    self.even.SetBackgroundColour((255,255,255))

def GetAttr(self, row, col, kind):
    attr = [self.even, self.odd][row % 2]
    attr.IncRef()
    return attr

def GetNumberRows(self):
    #print("# Rows:",len(self.data))
    return len(self.data)# - 1

def GetTypeName(self, row, col):
    #print(wx.grid.GRID_VALUE_STRING)
    return wx.grid.GRID_VALUE_STRING

def GetNumberCols(self):
    #print("# Cols:",len(self.data.columns)+ 1)
    return len(self.data.columns) + 1
    #return len(self.data.columns) #+ 1

def IsEmptyCell(self, row, col):
    return False

def GetValue(self, row, col):
    if col == 0:
        try:
            
            return self.data.index[row]
            
        except:
            print("Row,Col(",row,col,")","OOB")
            return ""
    else:
        try:
            
            return str(self.data.iloc[row, col - 1])
            
        except:
            print("Row,Col(",row,col,")","OOB")
            return ""
    
   
def GetColLabelValue(self, col):
    if col == 0:
        if self.data.index.name is None:
            return 'Index'
        else:
            return self.data.index.name
    return self.data.columns[col - 1]

   
def ResetView(self, grid):
    """
    (wxGrid) -> Reset the grid view.   Call this to
    update the grid if rows and columns have been added or deleted
    """
    print('Old::' , self._rows, self._cols)
    print('New::' , self.GetNumberRows(),self.GetNumberCols())
    print(data)

    grid.BeginBatch()
    for current, new, delmsg, addmsg in [
        (self._rows, self.GetNumberRows(), gridlib.GRIDTABLE_NOTIFY_ROWS_DELETED, gridlib.GRIDTABLE_NOTIFY_ROWS_APPENDED),
        (self._cols, self.GetNumberCols(), gridlib.GRIDTABLE_NOTIFY_COLS_DELETED, gridlib.GRIDTABLE_NOTIFY_COLS_APPENDED),
    ]:
        
        if new < current:
            msg = gridlib.GridTableMessage(self,delmsg,new,current-new)
            #grid.ProcessTableMessage(msg)
            self.GetView().ProcessTableMessage(msg)
            print("OvN:",self._rows,self.GetNumberRows())
            return True
        if new > current:
            msg = gridlib.GridTableMessage(self,addmsg,new-current)
            self.GetView().ProcessTableMessage(msg)
            grid.ProcessTableMessage(msg)
            #self.UpdateValues(grid)

            msg = gridlib.GridTableMessage(self, gridlib.GRIDTABLE_REQUEST_VIEW_GET_VALUES)
            grid.ProcessTableMessage(msg)
            print("OvN:",self._rows,self.GetNumberRows())
    grid.EndBatch()
       
    self._rows = self.GetNumberRows()
    self._cols = self.GetNumberCols()
    # update the column rendering plugins
    #self._updateColAttrs(grid)
   
    # XXX
    # Okay, this is really stupid, we need to "jiggle" the size
    # to get the scrollbars to recalibrate when the underlying
    # grid changes.
    h,w = grid.GetSize()
    grid.SetSize((h+1, w))
    grid.SetSize((h, w))
    grid.ForceRefresh()
    
    def UpdateValues(self, grid):#self, grid
        """Update all displayed values"""
        # This sends an event to the grid table to update all of the values
        msg = gridlib.GridTableMessage(self, gridlib.GRIDTABLE_REQUEST_VIEW_GET_VALUES)
        grid.table.ProcessTableMessage(msg)

class DataGrid(gridlib.Grid):
def __init__(self, parent, data, lc, tc): # data
    gridlib.Grid.__init__(self, parent, - 1) #,colnames,-1 # data
   
    self.lc = lc
    self.tc = tc
    self.table = DataTable(data)
  
    self.SetTable(self.table, True)
    self.Bind(gridlib.EVT_GRID_LABEL_RIGHT_CLICK, self.OnLabelRightClicked)
    self.Bind(gridlib.EVT_GRID_CELL_RIGHT_CLICK, self.OnCellRightClick)
    self.Bind(gridlib.EVT_GRID_CELL_CHANGED, self.onCellChanged) #wx.grid
    
def Reset(self):
    """reset the view based on the data in the table.  Call
    this when rows are added or destroyed"""
    self.table.ResetView(self)

def OnCellRightClick(self, event):
    print ("OnCellRightClick: (%d,%d)\n" % (event.GetRow(), event.GetCol()))

def OnLabelRightClicked(self, evt):
    row, col = evt.GetRow(), evt.GetCol()
    if row == -1: print("col")#self.colPopup(col, evt)
    elif col == -1: self.rowPopup(row, evt)

def rowPopup(self, row, evt):
    """(row, evt) -> display a popup menu when a row label is right clicked"""
    appendID = wx.Window.NewControlId()#wx.NewId()
    deleteID = wx.Window.NewControlId()#wx.NewId()
    x = self.GetRowSize(row)/2
    if not self.GetSelectedRows():
        self.SelectRow(row)
    menu = wx.Menu()
    xo, yo = evt.GetPosition()
    menu.Append(appendID, "Append Row")
    menu.Append(deleteID, "Delete Row(s)")

    def append(event, self=self, row=row):#event, self=self, row=row
        global data
        #print("Append")
        #self.table.AppendRows(row)
        dlg = wx.TextEntryDialog(self,'Enter a new Key ID to insert into the ' + str("'") + data.columns[0] + str("'") + ' column.', 'Insert New Record')
        dlg.SetValue("")
        if dlg.ShowModal() == wx.ID_OK:
            val = dlg.GetValue()
            #data[~pd.isnull(data).all(1)].fillna('')
            #data['tables_id'].apply('(g)'.format)
            data.loc[data.iloc[-1].name + 1,:] = ""
            data.iloc[data.shape[0]-1,0] = str(val)
            self.Reset()
            #print(data)
        #self.data = DataTable(data)
        
    def delete(event, self=self, row=row):#event, self=self, row=row
        global data

        rows = self.GetSelectedRows()
        data.drop(data.index[rows],inplace=True)
        print (data)
        self.Reset()

       
    self.Bind(wx.EVT_MENU, append, id=appendID)
    self.Bind(wx.EVT_MENU, delete, id=deleteID)
    self.PopupMenu(menu, wx.Point(round(x), round(yo)))
    menu.Destroy()
class MainFrame(wx.Frame):
def __init__(self, parent, data): # (self, parent, data):
    wx.Frame.__init__(self, parent, -1, "Varkey Foundation") #, size=(640,480))
    #Create a panel
    self.p = wx.Panel(self)
    self.Maximize(True)
    
    #Create blank dataframe
    data = pd.DataFrame() #pd.DataFrame(np.random.randint(0,100,size=(200, 5)),columns=list('EFGHD')
    #data.reset_index(drop=True, inplace=True)
    self.data = DataTable(data)
    
    self.nb = wx.Notebook(self.p)
    self.p.SetBackgroundColour( wx.Colour( 0, 0, 0 ) ) # 38,38,38
    self.nb.SetBackgroundColour(wx.Colour(58, 56, 56) )
    #self.SetBackgroundColour( wx.Colour( 255, 255, 56 ) )

    #create the page windows as children of the notebook
    self.page1 = PageOne(self.nb)
    self.page2 = PageTwo(self.nb)
    self.page3 = PageThree(self.nb)

    # add the pages to the notebook with the label to show on the tab
    self.nb.AddPage(self.page1, "Data")
    self.nb.AddPage(self.page2, "Analyze")
    self.nb.AddPage(self.page3, "Change Log") 

    #CreateFonts
    self.b_font = wx.Font(14,wx.ROMAN,wx.NORMAL,wx.BOLD, True)
    self.lbl_font = wx.Font(14,wx.ROMAN,wx.NORMAL,wx.NORMAL, True)
    self.cb_font = wx.Font(11,wx.SCRIPT,wx.ITALIC,wx.NORMAL, True)
    self.h_font = wx.Font(18,wx.DECORATIVE,wx.ITALIC,wx.BOLD, True)

    #Create username textcontrol <<<<<<<<<<<< Passed to grid class
    self.tc_user =wx.TextCtrl(self.p,value='cmccall95',size = (130,25))
    self.tc_password =wx.TextCtrl(self.p,value='Achilles95', style=wx.TE_PASSWORD | wx.TE_PROCESS_ENTER,size = (130,25))
    self.tc_password.Bind(wx.EVT_TEXT_ENTER,self.onLogin)
    self.tc_user.SetFont(self.cb_font)
    self.tc_password.SetFont(self.cb_font)

    #Create Change log lstCtrl <<<<<<<<<<<< Passed to grid class
    self.lc_change = wx.ListCtrl(self.p,-1,style = wx.TE_MULTILINE | wx.LC_REPORT | wx.LC_VRULES)
    self.lc_change.InsertColumn(0,"User ID")
    self.lc_change.InsertColumn(1,"Status")
    self.lc_change.InsertColumn(2,"Description")
    self.lc_change.InsertColumn(3,"Date/Time")
    #Set column widths
    self.lc_change.SetColumnWidth(0, 75)
    self.lc_change.SetColumnWidth(1, 75)
    self.lc_change.SetColumnWidth(2, 450)
    self.lc_change.SetColumnWidth(3, 125)

    #Create the grid and continue layout 
    self.grid = DataGrid(self.page1, data, self.lc_change, self.tc_user)

#More layout code…

def onLoadNewData(self, event): #This is how I'm replacing the data in my table class
    global data

    self.Freeze()
    if self.combo_table.GetValue():
        #Connect to db
        self.connect_mysql()

        #Determine db table
        self.getTable()
      
        #Get new data
        sql_query = "SELECT * FROM " + tbl 
        self.cursor.execute(sql_query)
        temp = pd.read_sql(sql_query, con=self.db_con)
        temp.reset_index(drop=True, inplace=True)
        data = temp[~pd.isnull(temp).all(1)].fillna('')
        
        #Create title #if data:
        if not data.empty:
            self.title.SetLabel(str(self.combo_table.GetValue()))
            print(str(self.combo_table.GetValue()))
            self.grid.Destroy()
            self.grid = DataGrid(self.page1, data, self.lc_change, self.tc_user)
            #self.grid.HideCol(0)
            self.grid.AutoSizeColumns()
            
            #Insert grid into existing sizer
            self.p1_sizer.Insert(1,self.grid,1,wx.RIGHT| wx.LEFT|wx.EXPAND, 20)
            self.p1_sizer.Layout()
        #RESIZE
        else:
            print("Error:Dataframe is empty")
        self.close_connection()
    else:
        print('CANT BE BLANK')
    self.Thaw()

if __name__ == '__main__':
import sys
app = wx.App()
frame = MainFrame(None, sys.stdout) # (None, sys.stdout)
frame.Show(True)
app.MainLoop()