Grid control and SQLite database (Phoenix)

Keywords : Grid, SQLite, Database, Data tables.


Demonstrating :

Tested py3.x, wx4.x and Win10.

Are you ready to use some samples ? ;)

Test, modify, correct, complete, improve and share your discoveries ! (!)


A phone book GUI built in wxPython connected to database using data grid view

img_sample_one.png

ICON file : icon_wxWidgets.ico

DATABASE file : Data.zip

   1 # sample_one.py
   2 
   3 import wx
   4 import wx.grid
   5 import os
   6 import sqlite3
   7 import re
   8 import gettext
   9 
  10 # def connect
  11 # def data_rows_count
  12 # def fmtstr
  13 # def titling
  14 # def single_quote_remover
  15 # def single_quote_returner
  16 # class MyFrame
  17 # class MyDialog1
  18 # class MyApp
  19 
  20 cwd = os.path.abspath(os.curdir)
  21 
  22 #---------------------------------------------------------------------------
  23 
  24 def connect():
  25     """
  26     This is the sqlite3 connection.
  27     """
  28 
  29     con_str=cwd + '/Data/file.db'
  30     cnn = sqlite3.connect(con_str)
  31     return cnn
  32     cnn.close()
  33 
  34 #---------------------------------------------------------------------------
  35 
  36 def data_rows_count():
  37     """
  38     To count the rows in the database.
  39     """
  40 
  41     con = connect()
  42     cur=con.cursor()
  43     cur.execute("SELECT * FROM Phone")
  44     rows=cur.fetchall()
  45     i=0
  46     for r in rows:
  47         i+=1
  48     return i
  49 
  50 #---------------------------------------------------------------------------
  51 
  52 def fmtstr(fmt, strr):
  53     """
  54     To format some string !!!
  55     """
  56 
  57     res = []
  58     i = 0
  59     s=re.sub(r'[^\w]', '', strr)
  60     for c in fmt:
  61         if c == '#':
  62             res.append(s[i:i+1])
  63             i = i+1
  64         else:
  65             res.append(c)
  66     res.append(s[i:])
  67     return "".join(res)
  68 
  69 #---------------------------------------------------------------------------
  70 
  71 def titling(name):
  72     """
  73     To display the names and surnames in uppercase for 1st letter.
  74     """
  75 
  76     return name.title()
  77 
  78 #---------------------------------------------------------------------------
  79 
  80 def single_quote_remover(text):
  81     """
  82     To remove single quotes from entry to prevent SQL crash.
  83     """
  84 
  85     return text.replace ("'", "/")
  86 
  87 #---------------------------------------------------------------------------
  88 
  89 def single_quote_returner(text):
  90     """
  91     To display the single quote for the user ex: cote d'or as chocolat:)))
  92     """
  93 
  94     return text.replace("/", "'")
  95 
  96 #---------------------------------------------------------------------------
  97 
  98 class MyFrame(wx.Frame):
  99     """
 100     This is the parent frame.
 101     """
 102     def __init__(self, *args, **kwds):
 103         kwds["style"] = wx.DEFAULT_FRAME_STYLE
 104         wx.Frame.__init__(self, *args, **kwds)
 105 
 106         self.SetIcon(wx.Icon('icon_wxWidgets.ico'))
 107 
 108         self.frame_1_menubar = wx.MenuBar()
 109         wxglade_tmp_menu = wx.Menu()
 110         wxglade_tmp_menu.Append(1, _("Index"), "", wx.ITEM_NORMAL)
 111         self.frame_1_menubar.Append(wxglade_tmp_menu, _("Phone Book"))
 112         wxglade_tmp_menu = wx.Menu()
 113         wxglade_tmp_menu.Append(2, _("Message"), "", wx.ITEM_NORMAL)
 114         self.frame_1_menubar.Append(wxglade_tmp_menu, _("About"))
 115         self.SetMenuBar(self.frame_1_menubar)
 116         self.__set_properties()
 117         self.__do_layout()
 118 
 119         self.Bind(wx.EVT_MENU, self.open_dialog, id=1)
 120         self.Bind(wx.EVT_MENU, self.open_dialog1,id =2)
 121 
 122 
 123     def __set_properties(self):
 124         self.SetTitle(_("MyPhoneBook"))
 125         self.SetSize((400, 250))
 126         self.SetBackgroundColour(wx.Colour(255, 255, 255))
 127 
 128 
 129     def __do_layout(self):
 130         sizer_1 = wx.BoxSizer(wx.VERTICAL)
 131         self.SetSizer(sizer_1)
 132         self.Layout()
 133 
 134 
 135     def open_dialog(self, event):
 136         MyDialog1(self).Show()
 137 
 138 
 139     def open_dialog1(self,event):
 140         wx.MessageBox("A simple PhoneBook that resumes basic graphical database configuration\n\nEnjoy...!")
 141 
 142 #---------------------------------------------------------------------------
 143 
 144 class MyDialog1(wx.Dialog):
 145     """
 146     This is the PhoneBook dialog box...
 147     """
 148     def __init__(self, *args, **kwds):
 149         kwds["style"] = wx.DEFAULT_DIALOG_STYLE
 150         wx.Dialog.__init__(self, *args, **kwds)
 151 
 152         self.SetIcon(wx.Icon('icon_wxWidgets.ico'))
 153 
 154         self.label_10 = wx.StaticText(self, -1, _(" ID :"))
 155         self.txtID = wx.TextCtrl(self, -1, "")
 156         self.label_11 = wx.StaticText(self, -1, _(" Name :"))
 157         self.txtNAME = wx.TextCtrl(self, -1, "")
 158         self.label_12 = wx.StaticText(self, -1, _(" Surname :"))
 159         self.txtSURNAME = wx.TextCtrl(self, -1, "")
 160         self.label_13 = wx.StaticText(self, -1, _(" Number :"))
 161         self.txtNUMBER = wx.TextCtrl(self, -1, "")
 162         self.button_6 = wx.Button(self, -1, _("UPDATE"))
 163         self.button_5 = wx.Button(self, -1, _("ADD"))
 164         self.button_7 = wx.Button(self, -1, _("DELETE"))
 165         self.button_8 = wx.Button(self, -1, _("LOAD"))
 166         self.grid_1 = wx.grid.Grid(self, -1, size=(1, 1))
 167         self.label_14 = wx.StaticText(self, -1, _("  Search Name :"))
 168         self.txtSearch = wx.TextCtrl(self, -1, "")
 169         self.button_9 = wx.Button(self, -1, _(" Go"))
 170         self.button_10 = wx.Button(self, -1, _("Renumber"))
 171         self.txtNAME.SetFocus()
 172         self.button_6.Enabled=False
 173         self.txtID.Enabled=False
 174         self.__set_properties()
 175         self.__do_layout()
 176 
 177         self.Bind(wx.EVT_BUTTON, self.clk_add, self.button_5)
 178         self.Bind(wx.EVT_BUTTON, self.clk_update, self.button_6)
 179         self.Bind(wx.EVT_BUTTON, self.clk_delete, self.button_7)
 180         self.Bind(wx.EVT_BUTTON, self.clk_load, self.button_8)
 181         self.Bind(wx.EVT_BUTTON, self.clk_go, self.button_9)
 182         self.Bind(wx.EVT_BUTTON, self.clk_renumber, self.button_10)
 183 
 184 
 185     def refresh_data(self):
 186         cnn =connect()
 187         cur = cnn.cursor()
 188         cur.execute("SELECT * FROM Phone")
 189         rows=cur.fetchall()
 190         for i in range (0, len(rows)):
 191             for j in range(0, 4):
 192                 cell = rows[i]
 193                 self.grid_1.SetCellValue(i, j, str(cell[j]))
 194 
 195 
 196     def __set_properties(self):
 197         self.SetTitle(_("PyPhone"))
 198         self.SetSize((630, 560))
 199         self.txtID.SetMinSize((120, 27))
 200         self.txtNAME.SetMinSize((120, 27))
 201         self.txtSURNAME.SetMinSize((120, 27))
 202         self.txtNUMBER.SetMinSize((120, 27))
 203         r=data_rows_count()
 204         # This is to create the grid with same rows as database.
 205         self.grid_1.CreateGrid(r, 4)
 206         self.grid_1.SetColLabelValue(0, _("ID"))
 207         self.grid_1.SetColSize(0, 12)
 208         self.grid_1.SetColLabelValue(1, _("NAME"))
 209         self.grid_1.SetColSize(1, 150)
 210         self.grid_1.SetColLabelValue(2, _("SURNAME"))
 211         self.grid_1.SetColSize(2, 150)
 212         self.grid_1.SetColLabelValue(3, _("NUMBER"))
 213         self.grid_1.SetColSize(3, 150)
 214         self.txtSearch.SetMinSize((100, 27))
 215         self.refresh_data()
 216 
 217 
 218     def __do_layout(self):
 219         sizer_4 = wx.BoxSizer(wx.VERTICAL)
 220         grid_sizer_4 = wx.GridSizer(1, 4, 0, 0)
 221         grid_sizer_3 = wx.GridSizer(4, 3, 0, 0)
 222         sizer_4.Add((20, 20), 0, 0, 0)
 223         grid_sizer_3.Add(self.label_10, 0, 0, 0)
 224         grid_sizer_3.Add(self.txtID, 0, 0, 0)
 225         grid_sizer_3.Add(self.button_5, 0, 0, 0)
 226         grid_sizer_3.Add(self.label_11, 0, 0, 0)
 227         grid_sizer_3.Add(self.txtNAME, 0, 0, 0)
 228         grid_sizer_3.Add(self.button_6, 0, 0, 0)
 229         grid_sizer_3.Add(self.label_12, 0, 0, 0)
 230         grid_sizer_3.Add(self.txtSURNAME, 0, 0, 0)
 231         grid_sizer_3.Add(self.button_7, 0, 0, 0)
 232         grid_sizer_3.Add(self.label_13, 0, 0, 0)
 233         grid_sizer_3.Add(self.txtNUMBER, 0, 0, 0)
 234         grid_sizer_3.Add(self.button_8, 0, 0, 0)
 235         sizer_4.Add(grid_sizer_3, 1, wx.EXPAND, 0)
 236         sizer_4.Add(self.grid_1, 1, wx.EXPAND, 0)
 237         sizer_4.Add((20, 20), 0, 0, 0)
 238         grid_sizer_4.Add(self.label_14, 0, wx.ALIGN_CENTER_VERTICAL, 0)
 239         grid_sizer_4.Add(self.txtSearch, 0, wx.ALIGN_CENTER_VERTICAL, 0)
 240         grid_sizer_4.Add(self.button_9, 0, wx.ALIGN_CENTER_VERTICAL, 0)
 241         grid_sizer_4.Add(self.button_10, 0, wx.ALIGN_CENTER_VERTICAL, 0)
 242         sizer_4.Add(grid_sizer_4, 1, wx.EXPAND, 0)
 243         self.SetSizer(sizer_4)
 244         self.Layout()
 245 
 246 
 247     def clear_grid(self):
 248         self.txtID.Value=""
 249         self.txtNAME.Value=""
 250         self.txtSURNAME.Value=""
 251         self.txtNUMBER.Value=""
 252 
 253 
 254     def auto_number(self):
 255         j=data_rows_count()
 256         return j+1
 257 
 258 
 259     def clk_add(self, event):
 260         if self.txtNAME.Value == "" or self.txtSURNAME.Value == "" or self.txtNUMBER.Value == "":
 261             wx.MessageBox("Some Fields Are Empty!")
 262         else:
 263             the_id=str(self.auto_number())
 264             the_name=single_quote_remover(str(self.txtNAME.Value))
 265             the_surname=single_quote_remover(str(self.txtSURNAME.Value))
 266             # Set the format here to the country u want.
 267             num=fmtstr('##-##-##-##-##',(str(self.txtNUMBER.Value)))
 268             name=titling(the_name)
 269             surname=titling(the_surname)
 270             self.grid_1.AppendRows(1)
 271             cnn = connect()
 272             cursor = cnn.cursor()
 273             add_many = "INSERT INTO Phone(ID, name, surname, telephone) VALUES(" + (the_id) + ",'" + (name) + "','" + (surname) + "','" + (num) + "')"
 274             cursor.execute(add_many)
 275             cnn.commit()
 276             cnn.close()
 277             self.refresh_data()
 278             self.clear_grid()
 279             self.txtNAME.SetFocus()
 280         event.Skip()
 281 
 282 
 283     def clk_update(self, event):
 284         try:
 285             num=fmtstr('##-##-##-##-##',str(self.txtNUMBER.Value))
 286             the_name=single_quote_remover(str(self.txtNAME.Value))
 287             the_surname=single_quote_remover(str(self.txtSURNAME.Value))
 288             name=titling(the_name)
 289             surname=titling(the_surname)
 290             row_index = self.grid_1. GetSelectedRows()[0]
 291             c=self.grid_1.GetCellValue(row_index,0)
 292             cnn=connect()
 293             cur=cnn.cursor()
 294             cur.execute("UPDATE Phone SET name = " + "'" + (name) + "'" + " ,surname=" + "'" + (surname) + "'" + ",telephone=" + "'" + (num) + "'" + "WHERE ID=" + "'" + str(c) + "'")
 295             cnn.commit()
 296             cnn.close()
 297             self.refresh_data()
 298             cnn.close()
 299             self.clear_grid()
 300             self.button_6.Enabled=False
 301             self.button_5.Enabled=True
 302             self.txtNAME.SetFocus()
 303             event.Skip()
 304         except IndexError:
 305             wx.MessageBox("you have lost focus on the row you wanted to edit")
 306 
 307 
 308     def clk_delete(self, event):
 309         try:
 310             lst = self.grid_1. GetSelectedRows()[0]
 311             c=self.grid_1.GetCellValue(lst, 0)
 312             cnn=connect()
 313             cur=cnn.cursor()
 314             cur.execute("DELETE FROM Phone WHERE ID=" + "'" + str(c) + "'")
 315             cnn.commit()
 316             cnn.close()
 317             self.grid_1.DeleteRows(lst, 1)
 318             self.refresh_data()
 319             self.txtNAME.SetFocus()
 320         except IndexError:
 321             wx.MessageBox("You Did Not Select Any Row To Delete!")
 322         event.Skip()
 323 
 324 
 325     def clk_load(self, event):
 326         try:
 327             row_index = self.grid_1.GetSelectedRows()[0]
 328             cell_value=[]
 329             for i in range(0, 4):
 330                 cell_value.append(self.grid_1.GetCellValue(row_index,i))
 331             self.txtID.Value= str(cell_value[0])
 332             self.txtNAME.Value=str(cell_value[1])
 333             self.txtSURNAME.Value=str(cell_value[2])
 334             self.txtNUMBER.Value=str(cell_value[3])
 335             self.button_6.Enabled=True
 336             self.button_5.Enabled=False
 337             self.txtNAME.SetFocus()
 338             event.Skip()
 339         except IndexError:
 340             wx.MessageBox("You Did Not Select Any Row To Load")
 341 
 342 
 343     def clk_go(self, event):
 344         r=data_rows_count()
 345         for e in range(0, r):
 346             for f in range(0, 4):
 347                 self.grid_1.SetCellValue(e, f, "")
 348         cnn=connect()
 349         cursor=cnn.cursor()
 350         cursor.execute("SELECT * FROM Phone WHERE name LIKE '%" + self.txtSearch.Value + "%'")
 351         cnn.commit()
 352         rows=cursor.fetchall()
 353         for i in range(len(rows)):
 354             for j in range(0, 4):
 355                 cell=rows[i]
 356                 self.grid_1.SetCellValue(i ,j, str(cell[j]))
 357         cnn.close()
 358         self.txtSearch.SetFocus()
 359         event.Skip()
 360 
 361 
 362     def clk_renumber(self, event):
 363         Backup_Messasse=wx.MessageDialog(None, "It Is Preferable To Backup Your Database Before You Continue! Do You Wish To Proceed?", 'Caution!', wx.YES_NO | wx.ICON_QUESTION)
 364         Response=Backup_Messasse.ShowModal()
 365         if(Response==wx.ID_NO):
 366             Backup_Messasse.Destroy()
 367         if(Response==wx.ID_YES):
 368             cnn = connect()
 369             cur = cnn.cursor()
 370             cur.execute("SELECT * FROM Phone")
 371             rows=cur.fetchall()
 372             i=0
 373             m=()
 374             for r in rows:
 375                 i+=1
 376                 s=str(r).replace(str(r[0]), str(i))
 377                 t=s.replace ("u'","'")
 378                 x=eval(t)
 379                 m+=(x,)
 380                 cur.execute("DELETE FROM Phone")
 381                 add_many="INSERT INTO Phone VALUES(?, ?, ?, ?)"
 382                 cur.executemany(add_many, m)
 383             wx.MessageBox("Renumbering Successful!")
 384             cur.execute("SELECT * FROM Phone")
 385             TheRows = cur.fetchall()
 386             for i in range(len(TheRows)):
 387                 for j in range(0, 4):
 388                     cell=TheRows[i]
 389                     self.grid_1.SetCellValue(i, j, str(cell[j]))
 390             cnn.commit()
 391             cnn.close()
 392             self.txtNAME.SetFocus()
 393             event.Skip()
 394 
 395 #---------------------------------------------------------------------------
 396 
 397 class MyApp(wx.App):
 398     def OnInit(self):
 399         gettext.install("app")
 400 
 401         # Set Current directory to the one containing this file.
 402         os.chdir(os.path.dirname(os.path.abspath(__file__)))
 403 
 404         wx.InitAllImageHandlers()
 405 
 406         self.SetAppName('MyPhoneBook')
 407 
 408         # Create the main window.
 409         frame_1 = MyFrame(None, wx.ID_ANY, "")
 410         self.SetTopWindow(frame_1)
 411 
 412         frame_1.Show()
 413         return True
 414 
 415 #---------------------------------------------------------------------------
 416 
 417 if __name__ == '__main__':
 418     app = MyApp()
 419     app.MainLoop()


Download source

source.zip


Additional Information

Link :

http://code.activestate.com/recipes/578665-a-wxpython-gui-to-create-sqlite3-databases/

http://code.activestate.com/recipes/578676-a-phone-book-gui-built-in-wxpython-connected-to-da/

https://github.com/ActiveState/code

https://wiki.wxpython.org/Create%20SQLite%20database%20easily%20%28Phoenix%29#preview

https://www.salstat.com/news/linking-wxgrid-to-sqlite-database-in-python-an-example

https://groups.google.com/forum/#%21topic/wx-users/hk2JidYWxIc

https://tutorial101.blogspot.com/2016/05/wxpython-phone-book-using-data-grid.html

https://stackoverflow.com/questions/54500052/how-to-add-the-database-to-wx-grid-of-wxpython

https://www.pythoncentral.io/series/python-sqlite-database-tutorial/

http://www.jacquet80.eu/blog/post/2008/10/31/241-sqlite

http://www.grroups.com/blog/sqlite-working-with-large-data-sets-in-python-effectively

https://www.sqlite.org/download.html

- - - - -

https://wiki.wxpython.org/TitleIndex

https://docs.wxpython.org/


Thanks to

Toufic Zaarour and Byblos Lebanon (sample_one.py coding), ActiveState and the wxPython community...


About this page

Date (d/m/y) Person (bot) Comments :

18/05/18 - Ecco (Created page and updated example for wxPython Phoenix).


Comments

- For any suggestions or improvements : touficmc@gmail.com

- blah, blah, blah...

Grid control and SQLite database (Phoenix) (last edited 2019-08-31 13:51:23 by Ecco)

NOTE: To edit pages in this wiki you must be a member of the TrustedEditorsGroup.