Welcome, guest | Sign In | My Account | Store | Cart

this GUI as simple as it is explains some basic but important graphical database interfacing; "Add", "Edit","Delete","Search" and few others along with a data grid view. in order to work create an sqlite3 database as follows:

data table : Phone, column 1 : ID, column 2 : name, column 3 : surname, column 4 : telephone.

save the sqlite3 file as file.db in a folder called Data and place it in the same directory as your python script.

if you want to create the sqlite3 database graphically use my previous post : http://code.activestate.com/recipes/578665-a-wxpython-gui-to-create-sqlite3-databases/

Also there is more: I did not use auto-number for 'id' because I also wanted to include in the code a renumbering script.

I am pleased to receive all the suggestions and improvements on this site or to my e-mail directly if this is convenient to you.

note: if you don't like the database table name, and columns name create your own but make sure to change them in the code as well! in the end life is great! remember that!

Python, 324 lines
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
#!/usr/bin/python

import wx
import wx.grid
import os
import sqlite3
import re
import string
import gettext

cwd = os.path.abspath(os.curdir)

def connect():#this is the sqlite3 connection
    con_str=cwd + '/Data/file.db'
    cnn = sqlite3.connect(con_str)
    return cnn
    cnn.close()

def data_rows_count():# to count the rows in the database
    con = connect()
    cur=con.cursor()
    cur.execute("SELECT * FROM Phone")
    rows=cur.fetchall()
    i=0
    for r in rows:
        i+=1
    return i

def fmtstr(fmt, strr):# to format some string!!!
    res = []
    i = 0
    s=re.sub(r'[^\w]','',strr)
    for c in fmt:
        if c == '#':
            res.append(s[i:i+1])
            i = i+1
        else:
            res.append(c)
    res.append(s[i:])
    return string.join(res)

def titling(name):# to display the names and surnames in uppercase for 1st letter
    return name.title()

def single_quote_remover(text):# to remove single quotes from entry to prevent SQL crash
    return text.replace ("'","/")

def single_quote_returner(text):# to display the single quote for the user ex: cote d'or as chocolat:)))
    return text.replace("/","'")

class MyFrame(wx.Frame):# this is the parent frame
    def __init__(self, *args, **kwds):
        kwds["style"] = wx.DEFAULT_FRAME_STYLE
        wx.Frame.__init__(self, *args, **kwds)
        self.frame_1_menubar = wx.MenuBar()
        wxglade_tmp_menu = wx.Menu()
        wxglade_tmp_menu.Append(1, _("Index"), "", wx.ITEM_NORMAL)
        self.frame_1_menubar.Append(wxglade_tmp_menu,_("Phone Book"))
        wxglade_tmp_menu = wx.Menu()
        wxglade_tmp_menu.Append(2, _("Message"), "", wx.ITEM_NORMAL)
        self.frame_1_menubar.Append(wxglade_tmp_menu,_("About"))
        self.SetMenuBar(self.frame_1_menubar)
        self.__set_properties()
        self.__do_layout()
        self.Bind(wx.EVT_MENU, self.open_dialog, id=1)
        self.Bind(wx.EVT_MENU,self.open_dialog1,id =2)

    def __set_properties(self):
        self.SetTitle(_("MyPhoneBook"))
        self.SetSize((555, 444))
        self.SetBackgroundColour(wx.Colour(255, 255, 255))

    def __do_layout(self):
        sizer_1 = wx.BoxSizer(wx.VERTICAL)
        self.SetSizer(sizer_1)
        self.Layout()

    def open_dialog(self, event):
        MyDialog1(self).Show()

    def open_dialog1(self,event):
        wx.MessageBox("A simple PhoneBook that resumes basic graphical database configuration\n\nEnjoy...!")

class MyDialog1(wx.Dialog):# this is the PhoneBook dialog box...
    def __init__(self, *args, **kwds):
        kwds["style"] = wx.DEFAULT_DIALOG_STYLE
        wx.Dialog.__init__(self, *args, **kwds)
        self.label_10 = wx.StaticText(self, -1, _(" ID"))
        self.txtID = wx.TextCtrl(self, -1, "")
        self.label_11 = wx.StaticText(self, -1, _(" Name"))
        self.txtNAME = wx.TextCtrl(self, -1, "")
        self.label_12 = wx.StaticText(self, -1, _(" Surname"))
        self.txtSURNAME = wx.TextCtrl(self, -1, "")
        self.label_13 = wx.StaticText(self, -1, _(" Number"))
        self.txtNUMBER = wx.TextCtrl(self, -1, "")
        self.button_6 = wx.Button(self, -1, _("UPDATE"))
        self.button_5 = wx.Button(self, -1, _("ADD"))
        self.button_7 = wx.Button(self, -1, _("DELETE"))
        self.button_8 = wx.Button(self, -1, _("LOAD"))
        self.grid_1 = wx.grid.Grid(self, -1, size=(1, 1))
        self.label_14 = wx.StaticText(self, -1, _("  Search Name:"))
        self.txtSearch = wx.TextCtrl(self, -1, "")
        self.button_9 = wx.Button(self, -1, _(" Go"))
        self.button_10 = wx.Button(self, -1, _("Renumber"))
        self.txtNAME.SetFocus()
        self.button_6.Enabled=False
        self.txtID.Enabled=False
        self.__set_properties()
        self.__do_layout()

        self.Bind(wx.EVT_BUTTON, self.clk_add, self.button_5)
        self.Bind(wx.EVT_BUTTON, self.clk_update, self.button_6)
        self.Bind(wx.EVT_BUTTON, self.clk_delete, self.button_7)
        self.Bind(wx.EVT_BUTTON, self.clk_load, self.button_8)
        self.Bind(wx.EVT_BUTTON, self.clk_go, self.button_9)
        self.Bind(wx.EVT_BUTTON, self.clk_renumber, self.button_10)
        
    def refresh_data(self):
        cnn =connect()
        cur = cnn.cursor()
        cur.execute("SELECT * FROM Phone")
        rows=cur.fetchall()
        for i in range (0,len(rows)):
            for j in range(0,4):
                cell = rows[i]
                self.grid_1.SetCellValue(i,j,str(cell[j]))

    def __set_properties(self):
        self.SetTitle(_("PyPhone"))
        self.SetSize((600, 550))
        self.txtID.SetMinSize((120, 27))
        self.txtNAME.SetMinSize((120, 27))
        self.txtSURNAME.SetMinSize((120, 27))
        self.txtNUMBER.SetMinSize((120, 27))
        r=data_rows_count()
        self.grid_1.CreateGrid(r, 4)#this is to create the grid with same rows as database
        self.grid_1.SetColLabelValue(0, _("ID"))
        self.grid_1.SetColSize(0, 12)
        self.grid_1.SetColLabelValue(1, _("NAME"))
        self.grid_1.SetColSize(1, 150)
        self.grid_1.SetColLabelValue(2, _("SURNAME"))
        self.grid_1.SetColSize(2, 150)
        self.grid_1.SetColLabelValue(3, _("NUMBER"))
        self.grid_1.SetColSize(3, 150)
        self.txtSearch.SetMinSize((100, 27))
        self.refresh_data()

    def __do_layout(self):
        sizer_4 = wx.BoxSizer(wx.VERTICAL)
        grid_sizer_4 = wx.GridSizer(1, 4, 0, 0)
        grid_sizer_3 = wx.GridSizer(4, 3, 0, 0)
        sizer_4.Add((20, 20), 0, 0, 0)
        grid_sizer_3.Add(self.label_10, 0, 0, 0)
        grid_sizer_3.Add(self.txtID, 0, 0, 0)
        grid_sizer_3.Add(self.button_5, 0, 0, 0)
        grid_sizer_3.Add(self.label_11, 0, 0, 0)
        grid_sizer_3.Add(self.txtNAME, 0, 0, 0)
        grid_sizer_3.Add(self.button_6, 0, 0, 0)
        grid_sizer_3.Add(self.label_12, 0, 0, 0)
        grid_sizer_3.Add(self.txtSURNAME, 0, 0, 0)
        grid_sizer_3.Add(self.button_7, 0, 0, 0)
        grid_sizer_3.Add(self.label_13, 0, 0, 0)
        grid_sizer_3.Add(self.txtNUMBER, 0, 0, 0)
        grid_sizer_3.Add(self.button_8, 0, 0, 0)
        sizer_4.Add(grid_sizer_3, 1, wx.EXPAND, 0)
        sizer_4.Add(self.grid_1, 1, wx.EXPAND, 0)
        sizer_4.Add((20, 20), 0, 0, 0)
        grid_sizer_4.Add(self.label_14, 0, wx.ALIGN_CENTER_VERTICAL, 0)
        grid_sizer_4.Add(self.txtSearch, 0, wx.ALIGN_CENTER_VERTICAL, 0)
        grid_sizer_4.Add(self.button_9, 0, wx.ALIGN_CENTER_VERTICAL, 0)
        grid_sizer_4.Add(self.button_10, 0, wx.ALIGN_CENTER_VERTICAL, 0)
        sizer_4.Add(grid_sizer_4, 1, wx.EXPAND, 0)
        self.SetSizer(sizer_4)
        self.Layout()

    def clear_grid(self):
        self.txtID.Value=""
        self.txtNAME.Value=""
        self.txtSURNAME.Value=""
        self.txtNUMBER.Value=""

    def auto_number(self):
        j=data_rows_count()
        return j+1  

    def clk_add(self, event):
        if self.txtNAME.Value == "" or self.txtSURNAME.Value == "" or self.txtNUMBER.Value == "":
            wx.MessageBox("Some Fields Are Empty!")
        else:
            the_id=str(self.auto_number())
            the_name=single_quote_remover(str(self.txtNAME.Value))
            the_surname=single_quote_remover(str(self.txtSURNAME.Value))
            num=fmtstr('##-######',(str(self.txtNUMBER.Value)))#set the format here to the country u want
            name=titling(the_name)
            surname=titling(the_surname)
            self.grid_1.AppendRows(1)
            cnn = connect()
            cursor = cnn.cursor()
            add_many = "INSERT INTO Phone(ID,name,surname,telephone) VALUES("+(the_id)+",'"+(name)+"','"+(surname)+"','"+(num)+"')"
            cursor.execute(add_many)
            cnn.commit()
            cnn.close()
            self.refresh_data()
            self.clear_grid()
            self.txtNAME.SetFocus()
        event.Skip()

    def clk_update(self, event):
        try:
            num=fmtstr('##-######',str(self.txtNUMBER.Value))
            the_name=single_quote_remover(str(self.txtNAME.Value))
            the_surname=single_quote_remover(str(self.txtSURNAME.Value))
            name=titling(the_name)
            surname=titling(the_surname)
            row_index = self.grid_1. GetSelectedRows()[0]
            c=self.grid_1.GetCellValue(row_index,0)
            cnn=connect()
            cur=cnn.cursor()
            cur.execute("UPDATE Phone SET name = "+ "'"+(name)+"'" + " ,surname="+ "'"+(surname)+"'" +",telephone=" + "'" +(num) + "'" + "WHERE ID="+"'" + str(c) + "'")
            cnn.commit()
            cnn.close()
            self.refresh_data()
            cnn.close()
            self.clear_grid()
            self.button_6.Enabled=False
            self.button_5.Enabled=True
            self.txtNAME.SetFocus()
            event.Skip()
        except IndexError:
            wx.MessageBox("you have lost focus on the row you wanted to edit")

    def clk_delete(self, event):
        try:
            lst = self.grid_1. GetSelectedRows()[0]
            c=self.grid_1.GetCellValue(lst,0)
            cnn=connect()
            cur=cnn.cursor()
            cur.execute("DELETE FROM Phone WHERE ID="+"'" + str(c) + "'")
            cnn.commit()
            cnn.close()
            self.grid_1.DeleteRows(lst,1)
            self.refresh_data()
            self.txtNAME.SetFocus()
        except IndexError:
            wx.MessageBox("You Did Not Select Any Row To Delete!")
        event.Skip()

    def clk_load(self, event):
        try:
            row_index = self.grid_1.GetSelectedRows()[0]
            cell_value=[]
            for i in range(0,4):
                cell_value.append(self.grid_1.GetCellValue(row_index,i))
            self.txtID.Value= str(cell_value[0])
            self.txtNAME.Value=str(cell_value[1])
            self.txtSURNAME.Value=str(cell_value[2])
            self.txtNUMBER.Value=str(cell_value[3])
            self.button_6.Enabled=True
            self.button_5.Enabled=False
            self.txtNAME.SetFocus()
            event.Skip()
        except IndexError:
            wx.MessageBox("You Did Not Select Any Row To Load")
            

    def clk_go(self, event):
        r=data_rows_count()
        for e in range(0,r):
            for f in range(0,4):
                self.grid_1.SetCellValue(e,f,"")
        cnn=connect()
        cursor=cnn.cursor()
        cursor.execute("SELECT * FROM Phone WHERE name LIKE '%"+self.txtSearch.Value+"%'") 
        cnn.commit()
        rows=cursor.fetchall()
        for i in range(len(rows)):
            for j in range(0,4):
                cell=rows[i]
                self.grid_1.SetCellValue(i,j,str(cell[j]))
        cnn.close()
        self.txtSearch.SetFocus()
        event.Skip()

    def clk_renumber(self, event):
        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)
        Response=Backup_Messasse.ShowModal()
        if(Response==wx.ID_NO):
            Backup_Messasse.Destroy()
        if(Response==wx.ID_YES):
            cnn = connect()
            cur = cnn.cursor()
            cur.execute("SELECT * FROM Phone")
            rows=cur.fetchall()
            i=0
            m=()
            for r in rows:
                i+=1
                s=str(r).replace(str(r[0]),str(i))
                t=s.replace ("u'","'")
                x=eval(t)
                m+=(x,)
                cur.execute("DELETE FROM Phone")
                add_many="INSERT INTO Phone VALUES(?,?,?,?)"
                cur.executemany(add_many,m)
            wx.MessageBox("Renumbering Successful!")
            cur.execute("SELECT * FROM Phone")
            TheRows = cur.fetchall()
            for i in range(len(TheRows)):
                for j in range(0,4):
                    cell=TheRows[i]
                    self.grid_1.SetCellValue(i,j,str(cell[j]))
            cnn.commit()
            cnn.close()
            self.txtNAME.SetFocus()
            event.Skip()

if __name__ == "__main__":
    gettext.install("app")
    app = wx.PySimpleApp(0)
    wx.InitAllImageHandlers()
    frame_1 = MyFrame(None, wx.ID_ANY, "")
    app.SetTopWindow(frame_1)
    frame_1.Show()
    app.MainLoop()

PS : I work on Linux Ubuntu but very easy to customize under Windows (just change the paths to the right format)

3 comments

Francis 9 years, 10 months ago  # | flag

this code is wooow..

Nitin Bansal 9 years, 10 months ago  # | flag

How to insert more than one button or label on a single cell of table(grid)

toufic zaarour (author) 9 years, 10 months ago  # | flag

I guess what you want to do is editing and deleting a row in the grid by clicking on a button that is in a cell of this same row.

Use grid events wx.grid.EVT_GRID_CELL_LEFT_CLICK() And associate this event to what you want to do. Or you should add buttons next to the rows of the grid and not inside of it. It means on each row addition create a button next to it. Other than that I really don't have any idea of how it might be done simply without rendering cells and complicating life more than it is already :). Check this

https://groups.Google.com/forum/#!topic/wx-users/hk2JidYWxIc