Threading while loading wxGrid - Python

125 Views Asked by At

I'm loading a wxGrid with values from a dataframe. I'm trying to use threading so my GUI doesn't freeze(some of the SQL tables are ~60,000 x 16 and take some time to load), but I guess I'm not understanding the concept. Sometimes it runs just fine works. Other times it crashes and exits with: Could not convert to integer: 3221226525. Path 'exitCode'. Value was either too large or too small for an Int32. I have also tried implementing another thread so that my wx.gauge loads properly. That causes it to fail more often. Can anyone give me any leads? I've been stuck on this for hours.

Snippets from my code: I also tried implementing another thread to update the wx.Gauge seperately with no success. The gauge updated but had very weird behavior. Any help would be greatly appreciated.

EDIT CODE ADDED Warning, this is probably going to be ugly

import sys
import os
import wx
import wx.grid as gridlib
import wx.lib.agw.pygauge as PG
import mysql.connector as sql
import MySQLdb
import datetime
import pandas as pd
from collections import OrderedDict
import threading #as thread
import time
from time import sleep
#import urllib

#result_available = threading.Event()
#df_data=pd.DataFrame()

class PageOne(wx.Panel):
    def __init__(self, parent):
        wx.Panel.__init__(self, parent)
        #wx.StaticText(self, -1, "This is a PageOne object", (20,20))
class PageTwo(wx.Panel):
    def __init__(self, parent):
        wx.Panel.__init__(self, parent)
        #wx.StaticText(self, -1, "This is a PageTwo object", (40, 40))
class PageThree(wx.Panel):
    def __init__(self, parent):
        wx.Panel.__init__(self, parent)
        #wx.StaticText(self, -1, "This is a PageThree object", (60, 60))
class PageDynamic(wx.Panel):
    def __init__(self, parent):
        wx.Panel.__init__(self, parent)
        wx.StaticText(self, -1, "This is a Dynamic object", (60, 60))

class MainFrame(wx.Frame):
    def __init__(self, parent):
        wx.Frame.__init__(self, parent, title="Varkey Foundation") #none
        #Layout
        self.__DoLayout()

    def __DoLayout(self):
        self.SetBackgroundColour( wx.Colour( 58, 56, 56 ) )
        
        # Here we create a panel and a notebook on the panel
        self.p = wx.Panel(self)
        self.p.SetBackgroundColour( wx.Colour( 0, 0, 0 ) ) # 38,38,38
        #self.Show() <Layout Error when GUI is launched
        self.Maximize(True)
        self.nb = wx.Notebook(self.p)
        self.nb.SetBackgroundColour(wx.Colour(58, 56, 56) )

        #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 Title bmp
        ico = wx.Icon('varkey_bmp.bmp', wx.BITMAP_TYPE_ICO) 
        self.SetIcon(ico)

        # 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")

        #Create widgets for top sizer
        self.lbl_user = wx.StaticText(self.p,label="Username:")
        self.lbl_password = wx.StaticText(self.p,label="Password:")
        self.lbl_interaction = wx.StaticText(self.p,label="Interaction:")
        self.lbl_table = wx.StaticText(self.p,label="Table:")
        #SetForground colors
        self.lbl_user.SetForegroundColour((255,255,255))
        self.lbl_password.SetForegroundColour((255,255,255))
        self.lbl_interaction.SetForegroundColour((255,255,255))
        self.lbl_table.SetForegroundColour((255,255,255))

        #Set Fonts
        self.lbl_user.SetFont(self.lbl_font)
        self.lbl_password.SetFont(self.lbl_font)
        self.lbl_interaction.SetFont(self.lbl_font)
        self.lbl_table.SetFont(self.lbl_font)

        self.tc_user =wx.TextCtrl(self.p,size = (130,25))
        self.tc_password =wx.TextCtrl(self.p, 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)

        self.btn_login = wx.Button(self.p,label="Login", size=(105,25))
        self.btn_login.SetBackgroundColour(wx.Colour(198, 89, 17))
        self.btn_login.SetFont(self.b_font)
        self.btn_login.Bind(wx.EVT_BUTTON, self.onLogin) #connect_mysql

        self.btn_logout = wx.Button(self.p,label="Logout",size=(105,25))
        self.btn_logout.SetBackgroundColour(wx.Colour(192,0,0))
        self.btn_logout.SetFont(self.b_font)
        self.btn_logout.Bind(wx.EVT_BUTTON, self.onLogout)

        self.combo_interaction = wx.ComboBox(self.p, size = (160,25),style = wx.CB_READONLY | wx.CB_SORT | wx.CB_SORT)
        self.combo_interaction.Bind(wx.EVT_COMBOBOX, self.onComboInteraction)
        self.combo_table = wx.ComboBox(self.p, size = (160,25),style = wx.CB_READONLY | wx.CB_SORT | wx.CB_SORT)
        self.combo_table.Bind(wx.EVT_COMBOBOX, self.onHideCommands)
        self.combo_interaction.SetFont(self.cb_font)
        self.combo_table.SetFont(self.cb_font)
       
        #self.combo_table.Bind(wx.EVT_COMBOBOX         ,self.OnComboTable)
        self.btn_load  = wx.Button(self.p,label="Load Table", size=(105,25))
        self.btn_load.SetBackgroundColour(wx.Colour(31, 216, 6))
        self.btn_load.SetFont(self.b_font)
        self.btn_load.Bind(wx.EVT_BUTTON, self.onLoadData)
        
        #Create Widgets for bottom sizer
        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)

        #Add Row Button
        self.btn_new = wx.Button(self.page1,label="+", size = (35,25))
        self.btn_new.SetForegroundColour(wx.Colour(112,173,71))
        self.btn_new.SetFont(self.h_font)
        self.btn_new.Bind(wx.EVT_BUTTON, self.onInsertRecordBelow)

        #Page 1 - Create grids/sizers and add to notebook
        self.color1 = (0,0,0)
        self.title = wx.StaticText(self.page1,label="",style = wx.ALIGN_CENTER | wx.ST_NO_AUTORESIZE)
        self.title.SetForegroundColour((255,255,255))
        self.title.SetFont(self.h_font)
        self.data_grid = gridlib.Grid(self.page1)
        self.data_grid.CreateGrid(0,0) #219,16
        self.p1_sizer = wx.BoxSizer(wx.VERTICAL)
        self.p1_sizer.Add(self.title,0,wx.EXPAND,5)
        self.p1_sizer.Add(self.data_grid,3,wx.RIGHT |wx.LEFT |wx.EXPAND, 20)
        self.p1_sizer.Add(self.btn_new,-0,wx.ALIGN_CENTER,5)
        self.page1.SetSizer(self.p1_sizer)

        #Page 2 - Create grids/sizers and add to notebook #<<<<<<< Need to create correct table size
        self.analyze_grid = gridlib.Grid(self.page2)
        self.analyze_grid.CreateGrid(0, 10)

        self.p2_sizer = wx.BoxSizer(wx.VERTICAL)
        self.p2_sizer.Add(self.analyze_grid,1,wx.EXPAND)
        self.page2.SetSizer(self.p2_sizer)

        #Page 3 - Create Change Log
        self.log_grid = gridlib.Grid(self.page3)
        self.log_grid.CreateGrid(0, 9)
        self.log_grid.EnableEditing(False)
       
        self.p3_sizer = wx.BoxSizer(wx.VERTICAL)
        self.p3_sizer.Add(self.log_grid,1,wx.EXPAND)
        self.page3.SetSizer(self.p3_sizer)

        #Insert Image
        self.staticbitmap = wx.StaticBitmap(self.p)
        #browse = wx.Button(self.p, label='Browse')
        #browse.Bind(wx.EVT_BUTTON, self.OnBrowse)
        self.staticbitmap.SetBitmap(wx.Bitmap('varkey_logo2.jpg'))
        self

        #Create Filler text
        self.lbl_filler = wx.StaticText(self.p,label="",size = (125,20))
        #Create FlexGridSizers(For top half)
        self.left_fgs = wx.FlexGridSizer(3,4,25,15)
        self.left_fgs.AddMany([(self.lbl_user,1,wx.ALIGN_LEFT | wx.LEFT,15),(self.tc_user,1,wx.EXPAND),(self.lbl_interaction,1,wx.ALIGN_RIGHT|wx.RIGHT, 10),(self.combo_interaction,1,wx.EXPAND),
                               (self.lbl_password,1,wx.ALIGN_LEFT| wx.LEFT,15),(self.tc_password,1,wx.EXPAND),(self.lbl_table,1,wx.ALIGN_RIGHT|wx.RIGHT, 10),(self.combo_table),
                               (self.btn_login,2,wx.EXPAND),(self.btn_logout,1,wx.EXPAND),(self.lbl_filler,1,wx.EXPAND),(self.btn_load,1)])

        
        #Create Top Sizer and add FGS
        self.top_sizer = wx.BoxSizer(wx.HORIZONTAL)
        self.top_sizer.Add(self.left_fgs,proportion = 1, flag = wx.ALL|wx.EXPAND,border = 30)
        #self.top_sizer.Add(self.right_fgs,proportion = 1, flag = wx.TOP|wx.BOTTOM ,border = 30)
        self.top_sizer.Add(self.staticbitmap,2,wx.TOP | wx.RIGHT, border = 40) #30
        self.top_sizer.Add(self.lc_change,2,wx.RIGHT|wx.EXPAND ,30)
        
        #Create bottom sizer(For Grid)
        self.bottom_sizer = wx.BoxSizer(wx.VERTICAL)
        self.bottom_sizer.Add(self.nb,proportion = 5, flag = wx.LEFT |wx.RIGHT | wx.EXPAND,border = 30)
        
        #Create statusbar and progress bar
        self.gauge = wx.Gauge(self.p, range = 100, size = (400, 20),style =  wx.GA_HORIZONTAL)
        #self.gauge = PG.PyGauge(self.p, 0, size=(400, 25), style=wx.GA_HORIZONTAL)
        #self.gauge.SetDrawValue(draw=True, drawPercent=True, font=None, colour=wx.BLACK, formatString=None)
        #self.gauge.SetBackgroundColour(wx.WHITE)
        #self.gauge.SetBorderColor(wx.BLACK)
        
        self.dummylbl = wx.StaticText(self.p,label="",size = (5,20))
        self.status_sizer = wx.BoxSizer(wx.HORIZONTAL)
        self.status_sizer.Add(self.gauge, 1, wx.ALIGN_CENTER|wx.ALL, 5)
        self.status_sizer.Add(self.dummylbl, 0, wx.ALL, 5)
        
        # the layout
        self.mainsizer = wx.BoxSizer(wx.VERTICAL)
        self.mainsizer.Add(self.top_sizer,proportion = 0, flag = wx.ALL|wx.EXPAND,border = 5)
        self.mainsizer.Add(self.bottom_sizer,proportion = 1,flag = wx.ALL|wx.EXPAND,border = 5)
        self.mainsizer.Add(self.status_sizer,proportion =0,flag = wx.BOTTOM|wx.ALIGN_CENTER_HORIZONTAL, border = 15)
        self.p.SetSizerAndFit(self.mainsizer)
        #self.page3.Bind(wx.EVT_LEFT_DCLICK, self.dynamic_tab)
        self.gauge.Hide()
        self.hideWidgets()
        self.onHideCommands(self)
        
    def thread_start(self): #self,event
        th = threading.Thread(target=self.populateGrid) #args=(event,), args=(self,)
        th.start()

        #th.join()

        #result_available.wait()

    #def status_thread(self): #TRIED FEEDING STATUS BAR HERE
    #    thr = threading.Thread(target=self.update_statusbar)
    #    thr.start()
    #def update_statusbar(self):
        #self.gauge.SetValue((round(i/self.rows)*100))

    def hideWidgets(self): #Hide and disable widgets until login
            if self.btn_logout.IsShown(): #Initialize and logout
                self.btn_logout.Hide()
                self.btn_load.Disable()
                self.combo_interaction.Enable(False)
                self.combo_table.Enable(False)
                self.tc_user.Enable()
                self.tc_password.Enable()
                self.btn_login.Show()
            
            else:                       #When logged in
                self.btn_logout.Show()
                self.combo_interaction.Enable(True)
                self.combo_table.Enable(True)
                self.btn_login.Hide()
                self.tc_user.Disable()
                self.tc_password.Disable()

    def onHideCommands(self, event):
            cbval = self.combo_table.GetValue()
            if cbval:
                self.btn_load.Enable()
                self.btn_new.Enable()
            else:
                self.btn_load.Disable()
                self.btn_new.Disable()

    def onLogin(self,event):
        #Get permissions from SQL table
        self.tbl = 'permissions'
        self.connect_mysql()
        #try:
        sql_query = "SELECT * FROM " + self.tbl 

        try:
            self.cursor.execute(sql_query)
            num_fields = len(self.cursor.description)
            self.df_permissions = pd.read_sql(sql_query, con=self.db_con)
        except:
            self.stat = "ERROR"
            self.msg = "ERROR: Failed to Connect. Check your internet connection and try again."
            wx.MessageBox("Failed to Connect. Check your internet connection and try again.", 'CONNECTION ERROR',
                          wx.OK | wx.ICON_ERROR)
            self.updateStatus()
            return
       
        if(len(self.tc_user.GetValue()) > 0):
            id = str(self.tc_user.GetValue())
            base = r'^{}'
            expr = '(?:\s|^){}(?:,\s|$)'
            try:
                u = self.df_permissions[self.df_permissions.iloc[:,0].str.contains(base.format(''.join(expr.format(id))),na = False, case = False)].index.values[0]
                #u = ((self.df_permissions[self.df_permissions.iloc[:,0].str.match(self.tc_user.GetValue())].index).values)[0] #,na=False,case=False

                pwrd = (self.df_permissions.iloc[u,1])
            except:
                wx.MessageBox("Access denied. " + id + " is not an authorized user.", 'Access Denied',
                          wx.OK | wx.ICON_ERROR)
                return
            #If password is correct, make connection
            if(self.tc_password.GetValue() == pwrd):
                self.stat = "Successful"
                self.msg = "Access Granted."
                self.updateStatus()
                self.tbl = 'tables'
                sql_query = "SELECT * FROM " + self.tbl
                self.cursor.execute(sql_query)
                self.df_tables = pd.read_sql(sql_query, con=self.db_con)
                
                if str(self.df_permissions.iloc[u,2])=="ALL":
                    self.interactionlist = self.df_tables['Interaction'].to_list()
                    self.interaction_filtered = self.df_tables #< For dependent combobox
                else:
                    read_str = str(self.df_permissions.iloc[u,2])
                    read_str = read_str.replace(", ", ",")
                    read_tables = list(read_str.split(","))
                    self.interaction_filtered = self.df_tables[self.df_tables['Table_Name'].isin(read_tables)] 
                    self.interactionlist = self.interaction_filtered['Interaction'].to_list()
                   
                #Remove duplicates and create lists for combobox
                self.interactionlist = list(OrderedDict.fromkeys(self.interactionlist))
                self.combo_interaction.SetItems(self.interactionlist)

                self.hideWidgets()
            else:
                 Access Denied."  + "\n")
                self.stat = "ERROR"
                self.msg = "ERROR: Incorrect Password. Access Denied."
                self.updateStatus()
        else:
            self.stat = "ERROR"
            self.msg = "ERROR: Username cannot be blank!"
            self.updateStatus()

        self.buildChangeLog()
        self.close_connection()

    def onLogout(self,event):
        self.hideWidgets()
        self.destroy_Widgets()
          
    def updateStatus(self):
        #Update listControl
        self.lc_change.Append([self.tc_user.GetValue(),self.stat,self.msg, str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")) + "\n"])

        if self.lc_change.GetItemCount() > 0:
            self.lc_change.EnsureVisible(self.lc_change.GetItemCount() - 1)

        #Update Log Grid
        if self.stat == "UPDATE" or self.stat == "DELETE" or self.stat == "INSERT":
            self.log_grid.AppendRows(numRows = 1, updateLabels = True)
            r = self.log_grid.GetNumberRows() -1
            self.log_grid.SetCellValue(r,0,self.key_id.replace("'",""))
            self.log_grid.SetCellValue(r,1,self.tc_user.GetValue())
            self.log_grid.SetCellValue(r,2,self.action) #Action
            self.log_grid.SetCellValue(r,3,self.tbl)
            self.log_grid.SetCellValue(r,4,self.key_col) #'Column #target_col
            self.log_grid.SetCellValue(r,5,self.target_col) #'Old Value
            self.log_grid.SetCellValue(r,6,self.oVal.replace("'","")) #'New Value
            self.log_grid.SetCellValue(r,7,self.nVal.replace("'","")) #'New Value
            self.log_grid.SetCellValue(r,8,str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))) #Timestamp
        #Size the grid
            self.log_grid.AutoSizeColumns(True)
        
        #Set Font color
        #index = self.lc_change.GetItemCount()
        #item = self.lc_change.GetItem(index)
        #self.lc_change.SetItemTextColour(index,'red')
        #self.lc_change.InsertItem(index,item)
        #print(index)
   
    def destroy_Widgets(self):
        #Destroy grid
        self.Freeze()
        self.data_grid.Destroy()
        #Reset and insert blank grid
        self.tbl = "elite_advocacy"
        self.connect_mysql()
        sql_query = "SELECT * FROM " + self.tbl 
        self.cursor.execute(sql_query)
        num_fields = len(self.cursor.description)
        self.df_data = pd.read_sql(sql_query, con=self.db_con)
        
        rows, cols = (int(self.df_data.shape[0]),int(self.df_data.shape[1]))
        
        self.data_grid = gridlib.Grid(self.page1)
        self.data_grid.Bind(wx.grid.EVT_GRID_CELL_CHANGED, self.onCellChanged)
        self.data_grid.Bind(wx.grid.EVT_GRID_CELL_RIGHT_DCLICK,self.onDeleteRecord)
        self.data_grid.Bind(wx.grid.EVT_GRID_CELL_RIGHT_CLICK,self.rightClickMenu)
        self.data_grid.CreateGrid(rows,cols) 
        self.p1_sizer.Insert(1,self.data_grid,1,wx.RIGHT| wx.LEFT| wx.EXPAND, 20)
        self.p1_sizer.Layout()

        #Clear Rows on log Grid
        n = self.log_grid.GetNumberRows()
        if n > 0:
            self.log_grid.DeleteRows(0,n)
        # Clear rows on analyze Grid
        n = self.analyze_grid.GetNumberRows()
        if n > 0:
            self.analyze_grid.DeleteRows(0,n)

        #Clear list listCtrl
        x = self.lc_change.GetItemCount()
        for i in range(x):
            self.lc_change.DeleteItem(0)

        #Clear textCtrl and ComboBoxes
            self.tc_password.SetValue("")
            self.tc_user.SetValue("")
            self.title.SetLabel("")

            self.combo_table.Clear()
            self.combo_interaction.Clear()
            self.combo_interaction.SetValue("")
            self.combo_table.SetValue("")

            self.close_connection()
        self.Thaw()

    def buildChangeLog(self):
        self.tbl = 'change_log'
        sql_query = "SELECT * FROM " + self.tbl 
        self.cursor.execute(sql_query)
        num_fields = len(self.cursor.description)
        
        #Create Grid Headers
        try:
            field_names = [i[0] for i in self.cursor.description]
            for i,a in enumerate(field_names):
                self.log_grid.SetColLabelValue(i,a)     
            #Size the grid
            self.log_grid.AutoSizeColumns(True)
        except:
            pass

    def onComboInteraction(self, event): 
      self.tables_filtered = self.interaction_filtered[self.interaction_filtered['Interaction'].str.contains(self.combo_interaction.GetValue())]
      self.tableslist = self.tables_filtered['Table_Name'].to_list()
      self.combo_table.SetItems(self.tableslist)
      self.onHideCommands(event)

    def dynamic_tab(self, event):
        print('dynamic_tab()')
        dynamic_page = PageDynamic(self.nb)
        self.nb.AddPage(dynamic_page, "Page Dynamic")

    def getTable(self):
        #Determine SQL table from DataFrame
        self.tbl_input = self.combo_table.GetValue()
        r = ((self.df_tables[self.df_tables.iloc[:,2].str.contains(self.combo_table.GetValue(),na=False)].index).values)[0]
        self.tbl = (self.df_tables.iloc[r,3])
          
    def populateGrid(self): 
        t0 = time.time()
        self.rows, self.cols = (int(self.df_data.shape[0]),int(self.df_data.shape[1]))

        for i, seq in enumerate(self.df_data.index):
                for j, v in enumerate(self.df_data.columns):
                    self.data_grid.SetCellValue(i, j, str(self.df_data.iloc[i,j]))
                #self.gauge.SetValue(int(round(i/self.rows,2)*100)) #int(percentage*100)
        self.gauge.SetValue(0)
        self.gauge.Hide()
       
        #Size the grid
        self.data_grid.AutoSizeColumns(True)
        self.stat = "QUERY"
        self.msg = str(self.rows) + " loaded from " + self.tbl
        self.updateStatus()
        #Set title
        self.title.SetLabel(str(self.combo_table.GetValue()))
        t1 = time.time()
        print(t1-t0)

    def onLoadData(self,event):
        if self.combo_table.GetValue():

            #Establish Connection
            self.connect_mysql()

            #Get Table
            self.getTable()
            #self.testFunction()
            if self.tbl:
                #Get SQL Data
                t0 = time.time()
                self.gauge.Show()
                sql_query = "SELECT * FROM " + self.tbl 
                self.cursor.execute(sql_query)
                num_fields = len(self.cursor.description)
                temp = pd.read_sql(sql_query, con=self.db_con)
                self.df_data = temp[~pd.isnull(temp).all(1)].fillna('')
                pd.set_option('display.max_columns', None)

                #Destroy grid and insert new resized grid
                rows, cols = (int(self.df_data.shape[0]),int(self.df_data.shape[1]))
                self.data_grid.Destroy()
                self.data_grid = gridlib.Grid(self.page1)
                self.data_grid.Bind(wx.grid.EVT_GRID_CELL_CHANGED, self.onCellChanged)
                self.data_grid.Bind(wx.grid.EVT_GRID_CELL_RIGHT_DCLICK,self.onDeleteRecord)
                #self.data_grid.Bind(gridlib.EVT_GRID_CELL_RIGHT_CLICK,self.showPopupMenu)
                self.data_grid.CreateGrid(rows,cols) #self.data_grid.CreateGrid(219, 16)
                self.p1_sizer.Insert(1,self.data_grid,1,wx.RIGHT| wx.LEFT|wx.EXPAND, 20)
                self.p1_sizer.Layout()
               
                #Create Grid Headers
                field_names = [i[0] for i in self.cursor.description]
                for i,a in enumerate(field_names):
                    self.data_grid.SetColLabelValue(i,a)
                self.data_grid.AutoSizeColumns(True)

                #Populate Table
                #####START THREAD#########
                self.thread_start()
                
            else: #self.tbl variable is blank
                self.stat = "ERROR"
                self.msg = "ERROR: No table exists in mySQL for Table: " + "'" + str(self.combo_table.GetValue()) + "'"
                self.updateStatus()
                return
        else:
            self.stat = "ERROR"
            self.msg = "ERROR: Table Combobox is empty "
            self.updateStatus()
            return
                
    def connect_mysql(self):
            self.db_name = 'db'
            self.server = 'server'
            self.user_id = 'user1'
            self.pw = 'pwrd'
            
            try:
                self.db_con = MySQLdb.connect(user=self.user_id,password=self.pw,database=self.db_name,
                                        host=self.server,charset='utf8',autocommit=True)
                self.cursor = self.db_con.cursor()
            except:
                print("Error connecting")
    def close_connection(self):       
        try:
            self.db_con.close()
        except:
            pass
    
    def onCellChanged(self, evt):
        self.connect_mysql()
        try:
            self.key_id =  str("'") + self.data_grid.GetCellValue(evt.GetRow(),0) +  str("'")
            self.target_col = self.data_grid.GetColLabelValue(evt.GetCol())
            self.key_col = self.data_grid.GetColLabelValue(0)
            self.nVal = str("'") + self.data_grid.GetCellValue(evt.GetRow(),evt.GetCol()) +  str("'")
            sql_update = "UPDATE " + self.tbl + " SET " +  self.target_col + " = " + self.nVal + " WHERE " + self.key_col  +  " = " + self.key_id + ""
            print(sql_update)
            self.cursor.execute(sql_update)
            self.stat = "UPDATE"
            self.oVal = evt.GetString()
            self.action = "UPDATE"
            self.msg =  "Changed " + str("'") + self.oVal + str("'") + " to " + self.nVal + " for " + self.key_id + " in table: " + str("'") + self.tbl + str("'") 
            self.updateStatus()
        except:
            self.stat = "ERROR"
            self.msg = "ERROR: Failed to update SQL table. " + "'" + self.tbl + "'"
            self.updateStatus()
            self.db_con.rollback()

        self.close_connection()

    def onInsertRecordBelow(self, evt):
        self.key_id =  str("'") + self.data_grid.GetCellValue(evt.GetRow(),0) +  str("'")
        self.target_col = "" #self.data_grid.GetColLabelValue(evt.GetCol())
        self.key_col = self.data_grid.GetColLabelValue(0)
        self.del_row = evt.GetRow()
        dlg = wx.TextEntryDialog(self.p,'Enter a new Key ID to insert into the ' + str("'") + self.data_grid.GetColLabelValue(0) + str("'") + ' column.', 'Insert New Record')
        #dlg.SetValue("Default")
        if dlg.ShowModal() == wx.ID_OK:
            #print('You entered: %s\n' % dlg.GetValue())
            val = dlg.GetValue()

            #Check if it exists in database
            self.connect_mysql()
            checkRec = ("SELECT " + str(self.key_col) + "," + " COUNT(*) FROM " + str(self.tbl) + " WHERE " + str(self.key_col) + " = " +  "'" + str(val) 
                        + "'" + " GROUP BY " + str(self.key_col) + "")
            self.cursor.execute(checkRec)
            results = self.cursor.fetchall()
            row_count = self.cursor.rowcount
            if row_count > 0:
                print("Exists")
                self.stat = "ERROR"
                self.msg = "ERROR: INSERT FAILED. " + "'" +str(val) + "'" + " already exists in table: " + "'" + self.tbl + "'." + " Abort."
                self.close_connection()
                self.updateStatus()
                return
            else:
                try:
                    self.connect_mysql()
                    sql_update = ("INSERT INTO " + str(self.tbl) + "(" + self.key_col + ")" + "VALUES (" + str("'") + str(val) + str("'") + ")")
                    self.cursor.execute(sql_update)

                    #Append row to Grid
                    lRow = int(self.df_data.shape[0])
                    lCol = int(self.df_data.shape[1])
                    self.data_grid.InsertRows(lRow,1)
                    self.data_grid.SetCellValue(lRow, 0, str(val))
            
                    #Insert into Dataframe
                    self.df_data.append(pd.Series(dtype='object'), ignore_index=True)

                    #Update status
                    self.key_id = val
                    self.stat = "INSERT"
                    self.msg = "INSERTED record " + "'" + str(val) + "'" + " into table: "  + "'" + self.tbl + "'"
                    self.action = "INSERT"
                    self.nVal = ""
                    self.oVal = ""
                except:
                    self.db_con.rollback()
                    self.stat = "ERROR"
                    self.msg = "ERROR: Failed to INSERT record '" + str(val) + "'into table: " + "'" + self.tbl + "'"
        else:
            print("ABORTED")
        self.close_connection()
        self.updateStatus()
        dlg.Destroy
        
    def onDeleteRecord(self,evt):
       
        #Connect
        self.connect_mysql()

        #Delete from mySQL table
        try:
            self.nVal = ""
            sql_delete = "DELETE FROM " + self.tbl + " WHERE " + self.key_col + " = " + self.key_id + ""
            print (sql_delete)
            self.cursor.execute(sql_delete)
            self.db_con.commit()
            self.stat = "DELETE"
            self.oVal = ""
            self.action = "DELETE"
            self.msg = "Deleted Record ID: "  + self.key_id + " from " + str("'") + self.tbl + str("'")  
            
        #Delete from Grid
            self.data_grid.DeleteRows(self.del_row,1,True)
        except:
            self.stat = "ERROR"
            self.msg = "ERROR: Failed to Delete record from table: " + "'" + self.tbl + "'"
            self.db_con.rollback()

        self.close_connection()
        self.updateStatus() 

if __name__ == "__main__":
    app = wx.App(False)
    MainFrame(None).Show() # MainFrame().Show()
    app.MainLoop()

enter image description here

enter image description here

0

There are 0 best solutions below