List control and SQLite database (Phoenix)

Keywords : ListCtrl, SQLite, Database, Data tables, Load, Connect, Select, Insert, Update, Delete, Bitmap, ListCtrlAutoWidthMixin, Widget Inspection Tool (WIT).


Demonstrating :

Tested py3.x, wx4.x and Win10.

Are you ready to use some samples ? ;)

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


Example of Database Interaction :

First example

img_sample_one.png

   1 # sample_one.py
   2 
   3 """
   4 
   5 Authors      : Beppe and Guldo
   6                Updated and improved for wxPython Phoenix by Ecco
   7 Date (d/m/y) : 31/09/2007 (v0.0.1)
   8                01/06/2019 (v0.0.2)
   9 Version      : 0.0.2
  10 Web          : Python-it.org
  11 Description  : Shows some wx.ListCtrl utilities.
  12                Correct mode for Insert, Update and Delete
  13                record in a SQLite 3 database.
  14                How to call a function of a frame from
  15                another frame.
  16                It use Northwind database ah ah ah !!!
  17                Enjoy yourself !
  18 
  19 """
  20 
  21 import sys
  22 import os
  23 import time
  24 import sqlite3
  25 import re
  26 import wx
  27 import wx.lib.mixins.inspection
  28 import wx.lib.mixins.listctrl as listmix
  29 
  30 AppTitle = "List of employees"
  31 iEmployees = 0
  32 
  33 # class MyConnection
  34 # class MyInsertDlg
  35 # class MyUpdateDlg
  36 # class MyListCtrl
  37 # class MyMainFrame
  38 # class MyApp
  39 
  40 #-------------------------------------------------------------------------------
  41 
  42 class MyConnection(object):
  43     """
  44     ...
  45     """
  46     def __init__(self):
  47 
  48         #------------
  49 
  50         # Return database folder.
  51         self.database_dir = wx.GetApp().GetDatabaseDir()
  52 
  53         # Loading "northwind" file.
  54         dbFile = os.path.join(self.database_dir, "northwind.db")
  55 
  56         #------------
  57 
  58         # Create/connect to a permanent file database.
  59         # For temporary testing you can use memory only.
  60         # Open a connection to a DB in RAM :
  61         # self.con = lite.connect(":memory:")
  62         self.con = sqlite3.connect(dbFile, isolation_level=None,
  63                                    detect_types=sqlite3.PARSE_DECLTYPES)
  64 
  65         # Establish the cursor, needed to execute the connected db.
  66         self.cur = self.con.cursor()
  67 
  68         try:
  69             # Create/execute a table (ex : Employees).
  70             self.cur.execute("""CREATE TABLE IF NOT EXISTS Employees (
  71                                              EmployeeID INTEGER PRIMARY KEY,
  72                                              Surname TEXT,
  73                                              Firstname TEXT,
  74                                              Phone TEXT,
  75                                              Email TEXT)
  76                              """)
  77 
  78         except sqlite3.OperationalError:
  79             wx.LogMessage("Can not create the database, "
  80                           "maybe because it already exists ?")
  81             return
  82 
  83         # Important if you make changes to the database commits
  84         # current data to the db file (data is persistant now).
  85         self.con.commit()
  86 
  87     #---------------------------------------------------------------------------
  88 
  89     def OnQuery(self, sSQL):
  90         """
  91         Execute a query passed in the argument from the main frame.
  92         """
  93 
  94         self.cur.execute(sSQL)
  95         # Here create the recordset.
  96         # It's a list of a list...
  97         rsRecordSet = self.cur.fetchall()
  98 
  99         return rsRecordSet
 100 
 101 
 102     def OnQueryParameter(self, sSQL, sParameter):
 103         """
 104         ...
 105         """
 106 
 107         Parameter =(sParameter, )
 108 
 109         self.cur.execute(sSQL, Parameter)
 110         rsRecordSet = self.cur.fetchall()
 111 
 112         return rsRecordSet
 113 
 114 
 115     def OnQueryUpdate(self, sSQL, sParameter):
 116         """
 117         ...
 118         """
 119 
 120         self.cur.execute(sSQL, sParameter)
 121         rsRecordSet = self.cur.fetchall()
 122 
 123         return rsRecordSet
 124 
 125 
 126     def OnSqliteVersion(self):
 127         """
 128         Returns SQLite version.
 129         """
 130 
 131         self.cur.execute("SELECT SQLITE_VERSION()")
 132 
 133         version = self.cur.fetchone()
 134 
 135         return version
 136 
 137 
 138     def OnCloseDb(self):
 139         """
 140         Disconnect database connection.
 141         """
 142 
 143         # Disconnect from server.
 144         self.cur.close()
 145         self.con.close()
 146 
 147 #-------------------------------------------------------------------------------
 148 
 149 class MyInsertDlg(wx.Dialog):
 150     """
 151     ...
 152     """
 153     def __init__(self, caller_dlgInsert, title=""):
 154         wx.Dialog.__init__(self,
 155                            parent=caller_dlgInsert,
 156                            id=-1,
 157                            title="")
 158 
 159         #------------
 160 
 161         self.caller = caller_dlgInsert
 162 
 163         #------------
 164 
 165         # Return icons folder.
 166         self.icons_dir = wx.GetApp().GetIconsDir()
 167 
 168         #------------
 169 
 170         # Simplified init method.
 171         self.ConnectDb()
 172         self.SetProperties()
 173         self.CreateCtrls()
 174         self.BindEvents()
 175         self.DoLayout()
 176 
 177     #---------------------------------------------------------------------------
 178 
 179     def ConnectDb(self):
 180         """
 181         Connection to the database.
 182         """
 183 
 184         # Instance from Class MyConnection.
 185         self.con = MyConnection()
 186 
 187 
 188     def SetProperties(self):
 189         """
 190         Set the frame properties (title, icon, size...).
 191         """
 192 
 193         # Setting some frame properties.
 194         frameIcon = wx.Icon(os.path.join(self.icons_dir,
 195                                          "icon_wxWidgets.ico"),
 196                             type=wx.BITMAP_TYPE_ICO)
 197         self.SetIcon(frameIcon)
 198 
 199 
 200     def CreateCtrls(self):
 201         """
 202         Create some controls for my frame.
 203         """
 204 
 205         # wx.Font(pointSize, family, style, weight, underline, faceName)
 206         font = wx.SystemSettings.GetFont(wx.SYS_DEFAULT_GUI_FONT)
 207         font.SetWeight(wx.BOLD)
 208 
 209         #------------
 210 
 211         # Widgets.
 212         self.panel = wx.Panel(self)
 213 
 214         #--
 215 
 216         self.stSurname = wx.StaticText(self.panel, -1, "Surname :")
 217         self.stSurname.SetForegroundColour("gray")
 218         self.stSurname.SetFont(font)
 219 
 220         self.txSurname = wx.TextCtrl(self.panel, -1, "", size=(230, -1))
 221 
 222         #--
 223 
 224         self.stFirstname = wx.StaticText(self.panel, -1, "First name :")
 225         self.stFirstname.SetForegroundColour("gray")
 226         self.stFirstname.SetFont(font)
 227 
 228         self.txFirstname = wx.TextCtrl(self.panel, -1, "", size=(230, -1))
 229 
 230         #--
 231 
 232         message  = "- Max chars : 2."
 233 
 234         self.stPhone = wx.StaticText(self.panel, -1, "Phone :")
 235         self.stPhone.SetForegroundColour("gray")
 236         self.stPhone.SetFont(font)
 237 
 238         self.txPhone1 = wx.TextCtrl(self.panel, -1, "", size=(35, -1),
 239                                     style=wx.TE_CENTRE)
 240         self.txPhone1.SetMaxLength(2)
 241         self.txPhone1.SetForegroundColour("gray")
 242         self.txPhone1.SetBackgroundColour("yellow")
 243         self.txPhone1.SetFont(font)
 244 
 245         self.hyphen1 = wx.StaticText(self.panel, -1, "-")
 246 
 247         self.txPhone2 = wx.TextCtrl(self.panel, -1, "", size=(35, -1),
 248                                     style=wx.TE_CENTRE)
 249         self.txPhone2.SetMaxLength(2)
 250 
 251         self.hyphen2 = wx.StaticText(self.panel, -1, "-")
 252 
 253         self.txPhone3 = wx.TextCtrl(self.panel, -1, "", size=(35, -1),
 254                                     style=wx.TE_CENTRE)
 255         self.txPhone3.SetMaxLength(2)
 256 
 257         self.hyphen3 = wx.StaticText(self.panel, -1, "-")
 258 
 259         self.txPhone4 = wx.TextCtrl(self.panel, -1, "", size=(35, -1),
 260                                     style=wx.TE_CENTRE)
 261         self.txPhone4.SetMaxLength(2)
 262 
 263         self.hyphen4 = wx.StaticText(self.panel, -1, "-")
 264 
 265         self.txPhone5 = wx.TextCtrl(self.panel, -1, "", size=(35, -1),
 266                                     style=wx.TE_CENTRE)
 267         self.txPhone5.SetMaxLength(2)
 268 
 269         #--
 270 
 271         self.stEmail = wx.StaticText(self.panel, -1, "Email :")
 272         self.stEmail.SetForegroundColour("gray")
 273         self.stEmail.SetFont(font)
 274 
 275         self.txEmail = wx.TextCtrl(self.panel, -1, "", size=(230, -1))
 276 
 277         #--
 278 
 279         self.StaticSizer = wx.StaticBox(self.panel, -1, "")
 280 
 281         #--
 282 
 283         self.bntSave = wx.Button(self.panel, -1, "&Save")
 284         self.bntSave.SetToolTip("Save !")
 285 
 286         self.bntClose = wx.Button(self.panel, -1, "&Close")
 287         self.bntClose.SetToolTip("Close !")
 288 
 289 
 290     def BindEvents(self):
 291         """
 292         Bind all the events related to my frame.
 293         """
 294 
 295         self.txSurname.Bind(wx.EVT_TEXT, self.OnUpperCaseText)
 296         self.txFirstname.Bind(wx.EVT_TEXT, self.OnCapitalizeCaseText)
 297         self.txEmail.Bind(wx.EVT_TEXT, self.OnLowerCaseText)
 298 
 299         self.txPhone1.Bind(wx.EVT_CHAR, self.OnChar)
 300         self.txPhone2.Bind(wx.EVT_CHAR, self.OnChar)
 301         self.txPhone3.Bind(wx.EVT_CHAR, self.OnChar)
 302         self.txPhone4.Bind(wx.EVT_CHAR, self.OnChar)
 303         self.txPhone5.Bind(wx.EVT_CHAR, self.OnChar)
 304 
 305         self.Bind(wx.EVT_BUTTON, self.OnSave, self.bntSave)
 306         self.Bind(wx.EVT_BUTTON, self.OnExit, self.bntClose)
 307 
 308         self.Bind(wx.EVT_CLOSE, self.OnExit)
 309 
 310 
 311     def DoLayout(self):
 312         """
 313         Do layout.
 314         """
 315 
 316         # Sizers.
 317         mainSizer = wx.BoxSizer(wx.HORIZONTAL)
 318         textSizer = wx.FlexGridSizer(cols=2, vgap=5, hgap=5)
 319         textSizer.AddGrowableCol(1)
 320 
 321         buttonSizer = wx.StaticBoxSizer(self.StaticSizer, wx.VERTICAL)
 322 
 323         # Assign widgets to sizers.
 324 
 325         # textSizer.
 326         textSizer.Add(self.stSurname, 0, wx.ALIGN_CENTER_VERTICAL)
 327         textSizer.Add(self.txSurname, 0, wx.ALIGN_CENTER_VERTICAL|wx.EXPAND)
 328 
 329         textSizer.Add(self.stFirstname, 0, wx.ALIGN_CENTER_VERTICAL)
 330         textSizer.Add(self.txFirstname, 0, wx.ALIGN_CENTER_VERTICAL|wx.EXPAND)
 331 
 332         textSizer.Add(self.stPhone, 0, wx.ALIGN_CENTER_VERTICAL)
 333 
 334         ctrlSizer = wx.BoxSizer(wx.HORIZONTAL)
 335         ctrlSizer.Add(self.txPhone1, 1, wx.EXPAND)
 336         ctrlSizer.Add(self.hyphen1, 0, wx.LEFT|wx.RIGHT|wx.ALIGN_CENTER_VERTICAL, 5)
 337         ctrlSizer.Add(self.txPhone2, 1, wx.EXPAND)
 338         ctrlSizer.Add(self.hyphen2, 0, wx.LEFT|wx.RIGHT|wx.ALIGN_CENTER_VERTICAL, 5)
 339         ctrlSizer.Add(self.txPhone3, 1, wx.EXPAND)
 340         ctrlSizer.Add(self.hyphen3, 0, wx.LEFT|wx.RIGHT|wx.ALIGN_CENTER_VERTICAL, 5)
 341         ctrlSizer.Add(self.txPhone4, 1, wx.EXPAND)
 342         ctrlSizer.Add(self.hyphen4, 0, wx.LEFT|wx.RIGHT|wx.ALIGN_CENTER_VERTICAL, 5)
 343         ctrlSizer.Add(self.txPhone5, 1, wx.EXPAND)
 344 
 345         textSizer.Add(ctrlSizer, 1, wx.EXPAND)
 346         textSizer.Add(self.stEmail, 0, wx.ALIGN_CENTER_VERTICAL)
 347         textSizer.Add(self.txEmail, 0, wx.ALIGN_CENTER_VERTICAL|wx.EXPAND)
 348 
 349         # buttonSizer.
 350         buttonSizer.Add(self.bntSave)
 351         buttonSizer.Add((5, 5), -1)
 352         buttonSizer.Add(self.bntClose)
 353 
 354         # Assign to mainSizer the other sizers.
 355         mainSizer.Add(textSizer, 0, wx.ALL, 10)
 356         mainSizer.Add(buttonSizer, 0, wx.ALL, 10)
 357 
 358         # Assign to panel the mainSizer.
 359         self.panel.SetSizer(mainSizer)
 360         mainSizer.Fit(self)
 361         mainSizer.SetSizeHints(self)
 362 
 363 
 364     def FieldsControl(self):
 365         """
 366         ...
 367         """
 368 
 369         if len(self.txSurname.GetValue()) == 0:
 370                 wx.MessageBox('ATTENTION !\nThe "Surname" field is empty !',
 371                               AppTitle,
 372                               wx.OK | wx.ICON_INFORMATION)
 373 
 374                 self.txSurname.SetFocus()
 375 
 376                 return 0
 377 
 378         elif len(self.txFirstname.GetValue()) == 0:
 379                 wx.MessageBox('ATTENTION !\nThe "First name" field is empty !',
 380                               AppTitle,
 381                               wx.OK | wx.ICON_INFORMATION)
 382 
 383                 return 0
 384 
 385         elif len(self.txPhone1.GetValue()) < 2:
 386                 wx.MessageBox('ATTENTION !\nThe "Phone" field is empty or icomplete !',
 387                               "Phone number",
 388                               wx.OK | wx.ICON_INFORMATION)
 389 
 390                 return 0
 391 
 392         elif len(self.txPhone2.GetValue()) < 2:
 393                 wx.MessageBox('ATTENTION !\nThe "Phone" field is empty or icomplete !',
 394                               "Phone number",
 395                               wx.OK | wx.ICON_INFORMATION)
 396 
 397                 return 0
 398 
 399         elif len(self.txPhone3.GetValue()) < 2:
 400                 wx.MessageBox('ATTENTION !\nThe "Phone" field is empty or icomplete !',
 401                               "Phone number",
 402                               wx.OK | wx.ICON_INFORMATION)
 403 
 404                 return 0
 405 
 406         elif len(self.txPhone4.GetValue()) < 2:
 407                 wx.MessageBox('ATTENTION !\nThe "Phone" field is empty or icomplete !',
 408                               "Phone number",
 409                               wx.OK | wx.ICON_INFORMATION)
 410 
 411                 return 0
 412 
 413         elif len(self.txPhone5.GetValue()) < 2:
 414                 wx.MessageBox('ATTENTION !\nThe "Phone" field is empty or icomplete !',
 415                               "Phone number",
 416                               wx.OK | wx.ICON_INFORMATION)
 417 
 418                 return 0
 419 
 420         elif len(self.txEmail.GetValue()) == 0:
 421                 wx.MessageBox('ATTENTION !\nThe "Email" field is empty !',
 422                               AppTitle,
 423                               wx.OK | wx.ICON_INFORMATION)
 424 
 425                 return 0
 426 
 427 
 428     def OnSave(self, event):
 429         """
 430         ...
 431         """
 432 
 433         if self.FieldsControl() == 0:
 434 
 435             return
 436 
 437         else:
 438             sMessage = "Save new employee data ?"
 439             dlgAsk = wx.MessageDialog(None,
 440                                       sMessage,
 441                                       AppTitle,
 442                                       wx.YES_NO | wx.ICON_QUESTION)
 443 
 444             retCode = dlgAsk.ShowModal()
 445 
 446             if (retCode == wx.ID_YES):
 447                 sSurname = str(self.txSurname.GetValue())
 448                 sFirstname = str(self.txFirstname.GetValue())
 449                 #--
 450                 value1 = str(self.txPhone1.GetValue())
 451                 value2 = str(self.txPhone2.GetValue())
 452                 value3 = str(self.txPhone3.GetValue())
 453                 value4 = str(self.txPhone4.GetValue())
 454                 value5 = str(self.txPhone5.GetValue())
 455                 number = value1 + value2 + value3 + value4 + value5
 456                 sPhone = self.AddHyphens(number)
 457                 #--
 458                 sEmail = str(self.txEmail.GetValue())
 459 
 460                 InsertParameters = (sSurname,
 461                                     sFirstname,
 462                                     sPhone,
 463                                     sEmail)
 464 
 465                 sSQL = "INSERT INTO Employees (Surname, \
 466                                                Firstname, \
 467                                                Phone, \
 468                                                Email) \
 469                                        VALUES (?, ?, ?, ?)"
 470 
 471                 # Insert new data in the database.
 472                 self.con.OnQueryUpdate(sSQL, InsertParameters)
 473 
 474                 # Guldo... thank you !
 475                 self.caller.OnUpdateList()
 476 
 477                 wx.MessageBox("New data insert !",
 478                               AppTitle,
 479                               wx.OK | wx.ICON_INFORMATION)
 480 
 481             elif (retCode == wx.ID_NO):
 482                 wx.MessageBox("Insert operation aborted !",
 483                               AppTitle,
 484                               wx.OK | wx.ICON_INFORMATION)
 485 
 486             dlgAsk.Destroy()
 487             self.OnExit(self)
 488 
 489 
 490     def OnUpperCaseText(self, event):
 491         """
 492         ...
 493         """
 494 
 495         # Write in upper mode on widgets the call it.
 496         # Adapted from a script of David Hughes
 497         # Forestfield Software.
 498         # Retrive the widget.
 499         wdgControl = event.GetEventObject()
 500 
 501         # Retrive what we have write.
 502         retValue = wdgControl.GetValue()
 503 
 504         # Upper it.
 505         upValue = retValue.upper()
 506 
 507         if retValue != upValue:
 508             wdgControl.SetValue(upValue)
 509             # Insert it at the end.
 510             wdgControl.SetInsertionPointEnd()
 511 
 512         event.Skip()
 513 
 514 
 515     def OnLowerCaseText(self, event):
 516         """
 517         ...
 518         """
 519 
 520         # Write in lower mode on widgets the call it.
 521         # Adapted from a script of David Hughes
 522         # Forestfield Software.
 523         # Retrive the widget.
 524         wdgControl = event.GetEventObject()
 525 
 526         # Retrive what we have write.
 527         retValue = wdgControl.GetValue()
 528 
 529         # Lower it.
 530         upValue = retValue.lower()
 531 
 532         if retValue != upValue:
 533             wdgControl.SetValue(upValue)
 534             # Insert it at the end.
 535             wdgControl.SetInsertionPointEnd()
 536 
 537         event.Skip()
 538 
 539 
 540     def OnCapitalizeCaseText(self, event):
 541         """
 542         ...
 543         """
 544 
 545         # Write in capitalize mode on widgets the call it.
 546         # Adapted from a script of David Hughes
 547         # Forestfield Software.
 548         # Retrive the widget.
 549         wdgControl = event.GetEventObject()
 550 
 551         # Retrive what we have write.
 552         retValue = wdgControl.GetValue()
 553 
 554         # Capitalize it.
 555         upValue = retValue.lower().capitalize()
 556 
 557         if retValue != upValue:
 558             wdgControl.SetValue(upValue)
 559             # Insert it at the end.
 560             wdgControl.SetInsertionPointEnd()
 561 
 562         event.Skip()
 563 
 564 
 565     def OnChar(self, event):
 566         """
 567         Block non numbers.
 568         """
 569 
 570         # print("\ndef OnChar")
 571 
 572         key_code = event.GetKeyCode()
 573 
 574         # Allow ASCII numerics.
 575         if ord('0') <= key_code <= ord('9'):
 576             event.Skip()
 577             return
 578 
 579         # Allow decimal points.
 580         if key_code == ord('.'):
 581             event.Skip()
 582             return
 583 
 584         # Allow tabs, for tab navigation between TextCtrls.
 585         if key_code == ord('\t'):
 586             event.Skip()
 587             return
 588 
 589         # Enable backspace, del, left-right.
 590         # The values may be platform dependent.
 591         if key_code in (8, 127, 314, 316):
 592             event.Skip()
 593             return
 594 
 595         # Block everything else.
 596         return
 597 
 598 
 599     def AddHyphens(self, nb):
 600         """
 601         ...
 602         """
 603 
 604         phone = nb[0:2] + '-' + nb[2:4] + '-' + nb[4:6] + '-' + nb[6:8] + '-' + nb[8:10]
 605 
 606         return phone
 607 
 608 
 609     def OnExit(self, event):
 610         """
 611         ...
 612         """
 613 
 614         self.Destroy()
 615 
 616 #-------------------------------------------------------------------------------
 617 
 618 class MyUpdateDlg(wx.Dialog):
 619     """
 620     ...
 621     """
 622     def __init__(self, caller_dlgUpdate, title=""):
 623         wx.Dialog.__init__(self,
 624                            parent=caller_dlgUpdate,
 625                            id=-1,
 626                            title=title,
 627                            size=(400, 200))
 628 
 629         #------------
 630 
 631         self.caller = caller_dlgUpdate
 632 
 633         #------------
 634 
 635         # Return icons folder.
 636         self.icons_dir = wx.GetApp().GetIconsDir()
 637 
 638         #------------
 639 
 640         # Simplified init method.
 641         self.ConnectDb()
 642         self.SetProperties()
 643         self.CreateCtrls()
 644         self.BindEvents()
 645         self.DoLayout()
 646 
 647     #---------------------------------------------------------------------------
 648 
 649     def ConnectDb(self):
 650         """
 651         Connection to the database.
 652         """
 653 
 654         # Instance from Class MyConnection.
 655         self.con = MyConnection()
 656 
 657 
 658     def SetProperties(self):
 659         """
 660         Set the frame properties (title, icon, size...).
 661         """
 662 
 663         # Setting some frame properties.
 664         frameIcon = wx.Icon(os.path.join(self.icons_dir,
 665                                          "icon_wxWidgets.ico"),
 666                             type=wx.BITMAP_TYPE_ICO)
 667         self.SetIcon(frameIcon)
 668 
 669 
 670     def CreateCtrls(self):
 671         """
 672         Create some controls for my frame.
 673         """
 674 
 675         # wx.Font(pointSize, family, style, weight, underline, faceName)
 676         font = wx.SystemSettings.GetFont(wx.SYS_DEFAULT_GUI_FONT)
 677         font.SetWeight(wx.BOLD)
 678 
 679         #------------
 680 
 681         # Widgets.
 682         self.panel = wx.Panel(self)
 683 
 684         #--
 685 
 686         self.stEmployeeID = wx.StaticText(self.panel, -1, "ID :")
 687         self.stEmployeeID.SetForegroundColour("red")
 688         self.stEmployeeID.SetFont(font)
 689 
 690         self.txEmployeeID = wx.TextCtrl(self.panel, -1, "", size=(230, -1),
 691                                         style=wx.TE_READONLY | wx.TE_CENTRE)
 692         self.txEmployeeID.SetForegroundColour("white")
 693         self.txEmployeeID.SetBackgroundColour("#CD5C5C")
 694         self.txEmployeeID.SetFont(font)
 695 
 696         #--
 697 
 698         self.stSurname = wx.StaticText(self.panel, -1, "Surname :")
 699         self.stSurname.SetForegroundColour("gray")
 700         self.stSurname.SetFont(font)
 701 
 702         self.txSurname = wx.TextCtrl(self.panel, -1, "", size=(230, -1))
 703 
 704         #--
 705 
 706         self.stFirstname = wx.StaticText(self.panel, -1, "First name :")
 707         self.stFirstname.SetForegroundColour("gray")
 708         self.stFirstname.SetFont(font)
 709 
 710         self.txFirstname = wx.TextCtrl(self.panel, -1, "", size=(230, -1))
 711 
 712         #--
 713 
 714         self.stPhone = wx.StaticText(self.panel, -1, "Phone :")
 715         self.stPhone.SetForegroundColour("gray")
 716         self.stPhone.SetFont(font)
 717 
 718         self.txPhone1 = wx.TextCtrl(self.panel, -1, "", size=(35, -1),
 719                                     style=wx.TE_CENTRE)
 720         self.txPhone1.SetMaxLength(2)
 721         self.txPhone1.SetForegroundColour("gray")
 722         self.txPhone1.SetBackgroundColour("yellow")
 723         self.txPhone1.SetFont(font)
 724 
 725         self.hyphen1 = wx.StaticText(self.panel, -1, "-")
 726 
 727         self.txPhone2 = wx.TextCtrl(self.panel, -1, "", size=(35, -1),
 728                                     style=wx.TE_CENTRE)
 729         self.txPhone2.SetMaxLength(2)
 730 
 731         self.hyphen2 = wx.StaticText(self.panel, -1, "-")
 732 
 733         self.txPhone3 = wx.TextCtrl(self.panel, -1, "", size=(35, -1),
 734                                     style=wx.TE_CENTRE)
 735         self.txPhone3.SetMaxLength(2)
 736 
 737         self.hyphen3 = wx.StaticText(self.panel, -1, "-")
 738 
 739         self.txPhone4 = wx.TextCtrl(self.panel, -1, "", size=(35, -1),
 740                                     style=wx.TE_CENTRE)
 741         self.txPhone4.SetMaxLength(2)
 742 
 743         self.hyphen4 = wx.StaticText(self.panel, -1, "-")
 744 
 745         self.txPhone5 = wx.TextCtrl(self.panel, -1, "", size=(35, -1),
 746                                     style=wx.TE_CENTRE)
 747         self.txPhone5.SetMaxLength(2)
 748 
 749         #--
 750 
 751         self.stEmail = wx.StaticText(self.panel, -1, "Email :")
 752         self.stEmail.SetForegroundColour("gray")
 753         self.stEmail.SetFont(font)
 754 
 755         self.txEmail = wx.TextCtrl(self.panel, -1, "", size=(230, -1))
 756 
 757         #--
 758 
 759         self.StaticSizer = wx.StaticBox(self.panel, -1,"")
 760 
 761         #--
 762 
 763         self.bntSave = wx.Button(self.panel, -1, "&Save")
 764         self.bntSave.SetToolTip("Save !")
 765 
 766         self.bntClose = wx.Button(self.panel, -1, "&Close")
 767         self.bntClose.SetToolTip("Close !")
 768 
 769 
 770 
 771     def BindEvents(self):
 772         """
 773         Bind all the events related to my frame.
 774         """
 775 
 776         self.txSurname.Bind(wx.EVT_TEXT, self.OnUpperCaseText)
 777         self.txFirstname.Bind(wx.EVT_TEXT, self.OnCapitalizeCaseText)
 778         self.txEmail.Bind(wx.EVT_TEXT, self.OnLowerCaseText)
 779 
 780         self.txPhone1.Bind(wx.EVT_CHAR, self.OnChar)
 781         self.txPhone2.Bind(wx.EVT_CHAR, self.OnChar)
 782         self.txPhone3.Bind(wx.EVT_CHAR, self.OnChar)
 783         self.txPhone4.Bind(wx.EVT_CHAR, self.OnChar)
 784         self.txPhone5.Bind(wx.EVT_CHAR, self.OnChar)
 785 
 786         self.Bind(wx.EVT_BUTTON, self.OnSave, self.bntSave)
 787         self.Bind(wx.EVT_BUTTON, self.OnExit, self.bntClose)
 788 
 789         self.Bind(wx.EVT_CLOSE, self.OnExit)
 790 
 791 
 792     def DoLayout(self):
 793         """
 794         Do layout.
 795         """
 796 
 797         # Sizers.
 798         mainSizer = wx.BoxSizer(wx.HORIZONTAL)
 799         textSizer = wx.FlexGridSizer(cols=2, vgap=5, hgap=5)
 800         textSizer.AddGrowableCol(1)
 801 
 802         buttonSizer = wx.StaticBoxSizer(self.StaticSizer, wx.VERTICAL)
 803 
 804         # Assign widgets to sizers.
 805 
 806         # textSizer.
 807         textSizer.Add(self.stEmployeeID, 0, wx.ALIGN_CENTER_VERTICAL)
 808         textSizer.Add(self.txEmployeeID, 0, wx.ALIGN_CENTER_VERTICAL|wx.EXPAND)
 809 
 810         textSizer.Add(self.stSurname, 0, wx.ALIGN_CENTER_VERTICAL)
 811         textSizer.Add(self.txSurname, 0, wx.ALIGN_CENTER_VERTICAL|wx.EXPAND)
 812 
 813         textSizer.Add(self.stFirstname, 0, wx.ALIGN_CENTER_VERTICAL)
 814         textSizer.Add(self.txFirstname, 0, wx.ALIGN_CENTER_VERTICAL|wx.EXPAND)
 815 
 816         textSizer.Add(self.stPhone, 0, wx.ALIGN_CENTER_VERTICAL)
 817 
 818         ctrlSizer = wx.BoxSizer(wx.HORIZONTAL)
 819         ctrlSizer.Add(self.txPhone1, 1, wx.EXPAND)
 820         ctrlSizer.Add(self.hyphen1, 0, wx.LEFT|wx.RIGHT|wx.ALIGN_CENTER_VERTICAL, 5)
 821         ctrlSizer.Add(self.txPhone2, 1, wx.EXPAND)
 822         ctrlSizer.Add(self.hyphen2, 0, wx.LEFT|wx.RIGHT|wx.ALIGN_CENTER_VERTICAL, 5)
 823         ctrlSizer.Add(self.txPhone3, 1, wx.EXPAND)
 824         ctrlSizer.Add(self.hyphen3, 0, wx.LEFT|wx.RIGHT|wx.ALIGN_CENTER_VERTICAL, 5)
 825         ctrlSizer.Add(self.txPhone4, 1, wx.EXPAND)
 826         ctrlSizer.Add(self.hyphen4, 0, wx.LEFT|wx.RIGHT|wx.ALIGN_CENTER_VERTICAL, 5)
 827         ctrlSizer.Add(self.txPhone5, 1, wx.EXPAND)
 828 
 829         textSizer.Add(ctrlSizer, 1, wx.EXPAND)
 830 
 831         textSizer.Add(self.stEmail, 0, wx.ALIGN_CENTER_VERTICAL)
 832         textSizer.Add(self.txEmail, 0, wx.ALIGN_CENTER_VERTICAL|wx.EXPAND)
 833 
 834         # buttonSizer.
 835         buttonSizer.Add(self.bntSave)
 836         buttonSizer.Add((5, 5), -1)
 837         buttonSizer.Add(self.bntClose)
 838 
 839         # Assign to mainSizer the other sizers.
 840         mainSizer.Add(textSizer, 0, wx.ALL, 10)
 841         mainSizer.Add(buttonSizer, 0, wx.ALL, 10)
 842 
 843         # Assign to panel the mainSizer.
 844         self.panel.SetSizer(mainSizer)
 845         mainSizer.Fit(self)
 846         mainSizer.SetSizeHints(self)
 847 
 848 
 849     def FieldsControl(self):
 850         """
 851         ...
 852         """
 853 
 854         if len(self.txSurname.GetValue()) == 0:
 855                 wx.MessageBox('ATTENTION !\nThe "Surname" field is empty !',
 856                               AppTitle,
 857                               wx.OK | wx.ICON_INFORMATION)
 858 
 859                 self.txSurname.SetFocus()
 860 
 861                 return 0
 862 
 863         elif len(self.txFirstname.GetValue()) == 0:
 864                 wx.MessageBox('ATTENTION !\nThe "First name" field is empty !',
 865                               AppTitle,
 866                               wx.OK | wx.ICON_INFORMATION)
 867 
 868                 return 0
 869 
 870         elif len(self.txPhone1.GetValue()) < 2:
 871                 wx.MessageBox('ATTENTION !\nThe "Phone" field is empty or icomplete !',
 872                               "Phone number",
 873                               wx.OK | wx.ICON_INFORMATION)
 874 
 875                 return 0
 876 
 877         elif len(self.txPhone2.GetValue()) < 2:
 878                 wx.MessageBox('ATTENTION !\nThe "Phone" field is empty or icomplete !',
 879                               "Phone number",
 880                               wx.OK | wx.ICON_INFORMATION)
 881 
 882                 return 0
 883 
 884         elif len(self.txPhone3.GetValue()) < 2:
 885                 wx.MessageBox('ATTENTION !\nThe "Phone" field is empty or icomplete !',
 886                               "Phone number",
 887                               wx.OK | wx.ICON_INFORMATION)
 888 
 889                 return 0
 890 
 891         elif len(self.txPhone4.GetValue()) < 2:
 892                 wx.MessageBox('ATTENTION !\nThe "Phone" field is empty or icomplete !',
 893                               "Phone number",
 894                               wx.OK | wx.ICON_INFORMATION)
 895 
 896                 return 0
 897 
 898         elif len(self.txPhone5.GetValue()) < 2:
 899                 wx.MessageBox('ATTENTION !\nThe "Phone" field is empty or icomplete !',
 900                               "Phone number",
 901                               wx.OK | wx.ICON_INFORMATION)
 902 
 903                 return 0
 904 
 905         elif len(self.txEmail.GetValue()) == 0:
 906                 wx.MessageBox('ATTENTION !\nThe "Email" field is empty !',
 907                               AppTitle,
 908                               wx.OK | wx.ICON_INFORMATION)
 909 
 910                 return 0
 911 
 912 
 913     def OnSave(self, event):
 914         """
 915         ...
 916         """
 917 
 918         if self.FieldsControl()==0:
 919 
 920             return
 921 
 922         else:
 923             sMessage = "Save update data?"
 924             dlgAsk = wx.MessageDialog(None,
 925                                       sMessage,
 926                                       AppTitle,
 927                                       wx.YES_NO | wx.ICON_QUESTION)
 928 
 929             retCode = dlgAsk.ShowModal()
 930 
 931             if (retCode == wx.ID_YES):
 932 
 933                 sEmployeeID = str(self.txEmployeeID.GetValue())
 934                 sSurname = str(self.txSurname.GetValue())
 935                 sFirstname = str(self.txFirstname.GetValue())
 936                 #--
 937                 value1 = str(self.txPhone1.GetValue())
 938                 value2 = str(self.txPhone2.GetValue())
 939                 value3 = str(self.txPhone3.GetValue())
 940                 value4 = str(self.txPhone4.GetValue())
 941                 value5 = str(self.txPhone5.GetValue())
 942                 number = value1 + value2 + value3 + value4 + value5
 943                 sPhone = self.AddHyphens(number)
 944                 #--
 945                 sEmail = str(self.txEmail.GetValue())
 946 
 947                 UpdateParameters = (sSurname,
 948                                     sFirstname,
 949                                     sPhone,
 950                                     sEmail,
 951                                     sEmployeeID)
 952 
 953                 sSQL = "UPDATE Employees SET Surname = ?, \
 954                                              Firstname = ?, \
 955                                              Phone = ?, \
 956                                              Email = ? \
 957                                        WHERE EmployeeID = ?"
 958 
 959                 # Update the database.
 960                 self.con.OnQueryUpdate(sSQL, UpdateParameters)
 961 
 962                 # Guldo... thank you !
 963                 self.caller.OnUpdateList()
 964 
 965                 wx.MessageBox("Data update!",
 966                               AppTitle,
 967                               wx.OK | wx.ICON_INFORMATION)
 968 
 969             elif (retCode == wx.ID_NO):
 970                 wx.MessageBox("Update operation aborted!",
 971                               AppTitle,
 972                               wx.OK | wx.ICON_INFORMATION)
 973 
 974             dlgAsk.Destroy()
 975             self.OnExit(self)
 976 
 977 
 978     def OnUpperCaseText(self, event):
 979         """
 980         ...
 981         """
 982 
 983         # Write in upper mode on widgets the call it.
 984         # Adapted from a script of David Hughes
 985         # Forestfield Software.
 986         # Retrive the widget.
 987         wdgControl = event.GetEventObject()
 988 
 989         # Retrive what we have write.
 990         retValue = wdgControl.GetValue()
 991 
 992         # Upper it.
 993         upValue = retValue.upper()
 994 
 995         if retValue != upValue:
 996             wdgControl.SetValue(upValue)
 997             # Insert it at the end.
 998             wdgControl.SetInsertionPointEnd()
 999 
1000         event.Skip()
1001 
1002 
1003     def OnLowerCaseText(self, event):
1004         """
1005         ...
1006         """
1007 
1008         # Write in upper mode on widgets the call it.
1009         # Adapted from a script of David Hughes
1010         # Forestfield Software.
1011         # Retrive the widget.
1012         wdgControl = event.GetEventObject()
1013 
1014         # Retrive what we have write.
1015         retValue = wdgControl.GetValue()
1016 
1017         # Upper it.
1018         upValue = retValue.lower()
1019 
1020         if retValue != upValue:
1021             wdgControl.SetValue(upValue)
1022             # Insert it at the end.
1023             wdgControl.SetInsertionPointEnd()
1024 
1025         event.Skip()
1026 
1027 
1028     def OnCapitalizeCaseText(self, event):
1029         """
1030         ...
1031         """
1032 
1033         # Write in upper mode on widgets the call it.
1034         # Adapted from a script of David Hughes
1035         # Forestfield Software.
1036         # Retrive the widget.
1037         wdgControl = event.GetEventObject()
1038 
1039         # Retrive what we have write.
1040         retValue = wdgControl.GetValue()
1041 
1042         # Upper it.
1043         upValue = retValue.lower().capitalize()
1044 
1045         if retValue != upValue:
1046             wdgControl.SetValue(upValue)
1047             # Insert it at the end.
1048             wdgControl.SetInsertionPointEnd()
1049 
1050         event.Skip()
1051 
1052 
1053     def OnChar(self, event):
1054         """
1055         Block non numbers.
1056         """
1057 
1058         # print("\ndef OnChar")
1059 
1060         key_code = event.GetKeyCode()
1061 
1062         # Allow ASCII numerics.
1063         if ord('0') <= key_code <= ord('9'):
1064             event.Skip()
1065             return
1066 
1067         # Allow decimal points.
1068         if key_code == ord('.'):
1069             event.Skip()
1070             return
1071 
1072         # Allow tabs, for tab navigation between TextCtrls.
1073         if key_code == ord('\t'):
1074             event.Skip()
1075             return
1076 
1077         # Enable backspace, del, left-right.
1078         # The values may be platform dependent.
1079         if key_code in (8, 127, 314, 316):
1080             event.Skip()
1081             return
1082 
1083         # Block everything else.
1084         return
1085 
1086 
1087     def AddHyphens(self, nb):
1088         """
1089         ...
1090         """
1091 
1092         phone = nb[0:2] + '-' + nb[2:4] + '-' + nb[4:6] + '-' + nb[6:8] + '-' + nb[8:10]
1093 
1094         return phone
1095 
1096 
1097     def OnExit(self, event):
1098         """
1099         ...
1100         """
1101 
1102         self.Destroy()
1103 
1104 #-------------------------------------------------------------------------------
1105 
1106 class MyListCtrl(wx.ListCtrl, listmix.ListCtrlAutoWidthMixin):
1107     """
1108     ...
1109     """
1110     def __init__(self, parent, id, pos=wx.DefaultPosition,
1111                  size=wx.DefaultSize, style=0):
1112         super(MyListCtrl, self).__init__(parent, id, pos, size, style)
1113 
1114         #------------
1115 
1116         listmix.ListCtrlAutoWidthMixin.__init__(self)
1117 
1118         #------------
1119 
1120         # Simplified init method.
1121         self.CreateColumns()
1122         self.SetProperties()
1123 
1124     #---------------------------------------------------------------------------
1125 
1126     def CreateColumns(self):
1127         """
1128         Create columns for listCtrl.
1129         """
1130 
1131         # Add some columns (x4).
1132         self.InsertColumn(col=0,  heading="ID", format=wx.LIST_FORMAT_LEFT)
1133         self.InsertColumn(col=1,  heading="Surname", format=wx.LIST_FORMAT_LEFT)
1134         self.InsertColumn(col=2,  heading="First name", format=wx.LIST_FORMAT_LEFT)
1135         self.InsertColumn(col=3,  heading="Phone", format=wx.LIST_FORMAT_LEFT)
1136         self.InsertColumn(col=4,  heading="Email", format=wx.LIST_FORMAT_LEFT)
1137 
1138         #------------
1139 
1140         # ASTUCE (Tip) - ListCtrlAutoWidthMixin :
1141         # pour diminuer le scintillement des colonnes
1142         # lors du redimensionnement de la mainframe,
1143         # regler la derniere colonne sur une largeur elevee.
1144         # Vous devez toujours visualiser l'ascenseur horizontal.
1145 
1146         # Set the width of the columns (x4).
1147         # Integer, wx.LIST_AUTOSIZE or wx.LIST_AUTOSIZE_USEHEADER.
1148         self.SetColumnWidth(col=0,  width=50)
1149         self.SetColumnWidth(col=1,  width=130)
1150         self.SetColumnWidth(col=2,  width=130)
1151         self.SetColumnWidth(col=3,  width=110)
1152         self.SetColumnWidth(col=4,  width=2000)
1153 
1154 
1155     def SetProperties(self):
1156         """
1157         Set the list control properties (icon, font, size...).
1158         """
1159 
1160         # Font size and style for listCtrl.
1161         fontSize = self.GetFont().GetPointSize()
1162 
1163         # Text attributes for columns title.
1164         # wx.Font(pointSize, family, style, weight, underline, faceName)
1165         if wx.Platform in ["__WXMAC__", "__WXGTK__"]:
1166             boldFont = wx.Font(fontSize-1,
1167                                wx.DEFAULT,
1168                                wx.NORMAL,
1169                                wx.NORMAL,
1170                                False, "")
1171             self.SetForegroundColour("black")
1172             self.SetBackgroundColour("#ece9d8")   #ecf3fd
1173 
1174         else:
1175             boldFont = wx.Font(fontSize,
1176                                wx.DEFAULT,
1177                                wx.NORMAL,
1178                                wx.BOLD,
1179                                False, "")
1180             self.SetForegroundColour("#808080")
1181             self.SetBackgroundColour("#ece9d8")   #ecf3fd
1182 
1183         self.SetFont(boldFont)
1184 
1185 #-------------------------------------------------------------------------------
1186 
1187 class MyMainFrame(wx.Frame):
1188     """
1189     ...
1190     """
1191     def __init__(self, parent, id, title,
1192                  style=wx.DEFAULT_FRAME_STYLE |
1193                        wx.NO_FULL_REPAINT_ON_RESIZE |
1194                        wx.CLIP_CHILDREN):
1195         super(MyMainFrame, self).__init__(parent=None,
1196                                           id=-1,
1197                                           title=title,
1198                                           style=style)
1199 
1200         #------------
1201 
1202         # Returns application name.
1203         self.app_name = wx.GetApp().GetAppName()
1204         # Returns bitmaps folder.
1205         self.bitmaps_dir = wx.GetApp().GetBitmapsDir()
1206         # Returns icons folder.
1207         self.icons_dir = wx.GetApp().GetIconsDir()
1208 
1209         #------------
1210 
1211         # Simplified init method.
1212         self.ConnectDb()
1213         self.SetProperties()
1214         self.MakeMenuBar()
1215         self.MakeStatusBar()
1216         self.CreateCtrls()
1217         self.BindEvents()
1218         self.DoLayout()
1219 
1220         #------------
1221 
1222         # Clock.
1223         self.OnTimer(None)
1224 
1225         self.timer = wx.Timer(self)
1226         self.timer.Start(3000)
1227         self.Bind(wx.EVT_TIMER, self.OnTimer)
1228 
1229     #---------------------------------------------------------------------------
1230 
1231     def ConnectDb(self):
1232         """
1233         Connection to the database.
1234         """
1235 
1236         # Instance from Class MyConnection.
1237         self.con = MyConnection()
1238 
1239 
1240     def SetProperties(self):
1241         """
1242         Set the frame properties (title, icon, size...).
1243         """
1244 
1245         # Setting some frame properties.
1246         frameIcon = wx.Icon(os.path.join(self.icons_dir,
1247                                          "icon_wxWidgets.ico"),
1248                             type=wx.BITMAP_TYPE_ICO)
1249         self.SetIcon(frameIcon)
1250 
1251         #------------
1252 
1253         # Frame cursor.
1254         cursorHand = wx.Cursor(os.path.join(self.icons_dir,
1255                                             "hand.cur"),
1256                                type=wx.BITMAP_TYPE_CUR)
1257         self.SetCursor(cursorHand)
1258 
1259         #------------
1260 
1261         self.SetTitle(("%s") % (self.app_name))
1262 
1263 
1264     def MakeMenuBar(self):
1265         """
1266         Create the menu bar for my app.
1267         """
1268 
1269         # Set an icon to the exit/about menu item.
1270         emptyImg = wx.Bitmap(os.path.join(self.bitmaps_dir,
1271                                           "item_empty.png"),
1272                              type=wx.BITMAP_TYPE_PNG)
1273 
1274         exitImg = wx.Bitmap(os.path.join(self.bitmaps_dir,
1275                                          "item_exit.png"),
1276                             type=wx.BITMAP_TYPE_PNG)
1277 
1278         helpImg = wx.Bitmap(os.path.join(self.bitmaps_dir,
1279                                          "item_about.png"),
1280                             type=wx.BITMAP_TYPE_PNG)
1281 
1282         #------------
1283 
1284         # menu.
1285         mnuFile = wx.Menu()
1286         mnuInfo = wx.Menu()
1287 
1288         # mnuFile.
1289         # Show how to put an icon in the menu item.
1290         menuItem1 = wx.MenuItem(mnuFile, -1, "W&IT\tCtrl+Alt+I", "Widget Inspection Tool !")
1291         menuItem1.SetBitmap(emptyImg)
1292         mnuFile.Append(menuItem1)
1293         self.Bind(wx.EVT_MENU, self.OnOpenWidgetInspector, menuItem1)
1294 
1295         # Show how to put an icon in the menu item.
1296         menuItem2 = wx.MenuItem(mnuFile, wx.ID_EXIT, "&Quit\tCtrl+Q", "Close !")
1297         menuItem2.SetBitmap(exitImg)
1298         mnuFile.Append(menuItem2)
1299         self.Bind(wx.EVT_MENU, self.OnExit, menuItem2)
1300 
1301         # mnuInfo.
1302         # Show how to put an icon in the menu item.
1303         menuItem2 = wx.MenuItem(mnuInfo, wx.ID_ABOUT, "A&bout\tCtrl+A", "Info about developers !")
1304         menuItem2.SetBitmap(helpImg)
1305         mnuInfo.Append(menuItem2)
1306         self.Bind(wx.EVT_MENU, self.OnAbout, menuItem2)
1307 
1308         # menuBar.
1309         menubar = wx.MenuBar()
1310 
1311         # Add menu voices.
1312         menubar.Append(mnuFile, "&File")
1313         menubar.Append(mnuInfo, "I&nfo")
1314 
1315         self.SetMenuBar(menubar)
1316 
1317 
1318     def MakeStatusBar(self):
1319         """
1320         Create the status bar for my frame.
1321         """
1322 
1323         # Statusbar.
1324         self.myStatusBar = self.CreateStatusBar(1)
1325         self.myStatusBar.SetFieldsCount(2)
1326         self.myStatusBar.SetStatusWidths([-8, -4])
1327         self.myStatusBar.SetStatusText("", 0)
1328         self.myStatusBar.SetStatusText("Python-it.org.", 1)
1329 
1330 
1331     def CreateCtrls(self):
1332         """
1333         Create some controls for my frame.
1334         """
1335 
1336         # Font style for wx.StaticText.
1337         font = wx.SystemSettings.GetFont(wx.SYS_DEFAULT_GUI_FONT)
1338         font.SetWeight(wx.BOLD)
1339 
1340         #------------
1341 
1342         # Widgets.
1343         self.panel = wx.Panel(self)
1344 
1345         self.stEmployees = wx.StaticText(self.panel, -1, "Employees list :")
1346         self.stEmployees.SetForegroundColour("gray")
1347         self.stEmployees.SetFont(font)
1348 
1349         # Image list.
1350         self.il = wx.ImageList(16, 16, True)
1351 
1352         # Set an icon for the first column.
1353         self.bmp = wx.Bitmap(os.path.join(self.bitmaps_dir,
1354                                           "employee.png"),
1355                              type=wx.BITMAP_TYPE_PNG)
1356 
1357         # Add image to list.
1358         self.img_idx = self.il.Add(self.bmp)
1359 
1360         self.listCtrl = MyListCtrl(self.panel,
1361                                    -1,
1362                                    style=wx.LC_REPORT |
1363                                          wx.LC_SINGLE_SEL |
1364                                          wx.LC_VRULES |
1365                                          wx.BORDER_SUNKEN)
1366 
1367         # Assign the image list to it.
1368         self.listCtrl.SetImageList(self.il, wx.IMAGE_LIST_SMALL)
1369 
1370         # Retrieve data from the database.
1371         self.employeeData = self.OnLoadData()
1372 
1373         # Populate the wx.ListCtrl.
1374         for i in self.employeeData:
1375             index = self.listCtrl.InsertItem(self.listCtrl.GetItemCount(),
1376                                              ((str(i[0]))))
1377             self.listCtrl.SetItem(index, 1, i[1])
1378             self.listCtrl.SetItem(index, 2, i[2])
1379             self.listCtrl.SetItem(index, 3, i[3])
1380             self.listCtrl.SetItem(index, 4, i[4])
1381             self.listCtrl.SetItemImage(self.listCtrl.GetItemCount() - 1,
1382                                        self.img_idx)
1383 
1384             # Alternate the row colors of a ListCtrl.
1385             # Mike Driscoll... thank you !
1386             if index % 2:
1387                 self.listCtrl.SetItemBackgroundColour(index, "#ffffff")
1388             else:
1389                 self.listCtrl.SetItemBackgroundColour(index, "#ece9d8")   #ecf3fd
1390 
1391         self.stSearch = wx.StaticText(self.panel, -1, 'Search "Surname" :')
1392         self.txSearch = wx.TextCtrl(self.panel, -1, "", size=(100, -1))
1393         self.txSearch.SetToolTip("Search employee !")
1394 
1395         self.StaticSizer = wx.StaticBox(self.panel, -1, "Commands :")
1396         self.StaticSizer.SetForegroundColour("red")
1397         self.StaticSizer.SetFont(font)
1398 
1399         self.bntSearch = wx.Button(self.panel, -1, "&Search")
1400         self.bntSearch.SetToolTip("Search employee !")
1401 
1402         self.bntClear = wx.Button(self.panel, -1, "&Clear")
1403         self.bntClear.SetToolTip("Clear the search text !")
1404 
1405         self.bntShowAll = wx.Button(self.panel, -1, "&All")
1406         self.bntShowAll.SetToolTip("Show all !")
1407 
1408         self.bntNew = wx.Button(self.panel, -1, "&Insert")
1409         self.bntNew.SetToolTip("Insert a new employee !")
1410 
1411         self.bntEdit = wx.Button(self.panel, -1, "&Update")
1412         self.bntEdit.SetToolTip("Update selected employee !")
1413 
1414         self.bntDelete = wx.Button(self.panel, -1, "&Delete")
1415         self.bntDelete.SetToolTip("Delete selected employee !")
1416 
1417         self.bntClose = wx.Button(self.panel, -1, "&Quit")
1418         self.bntClose.SetToolTip("Close !")
1419 
1420 
1421     def BindEvents(self):
1422         """
1423         Bind all the events related to my frame.
1424         """
1425 
1426         self.txSearch.Bind(wx.EVT_TEXT, self.OnUpperCaseText)
1427 
1428         # Intercept the click on the wx.ListCtrl.
1429         self.Bind(wx.EVT_LIST_ITEM_SELECTED, self.OnItemSelected, self.listCtrl)
1430         self.Bind(wx.EVT_LIST_COL_BEGIN_DRAG, self.OnColBeginDrag, self.listCtrl)
1431         self.Bind(wx.EVT_LIST_ITEM_ACTIVATED, self.OnItemActivated, self.listCtrl)
1432 
1433         self.Bind(wx.EVT_BUTTON, self.OnSearch, self.bntSearch)
1434         self.Bind(wx.EVT_BUTTON, self.OnClear, self.bntClear)
1435         self.Bind(wx.EVT_BUTTON, self.OnShowAll, self.bntShowAll)
1436         self.Bind(wx.EVT_BUTTON, self.OnNew, self.bntNew)
1437         self.Bind(wx.EVT_BUTTON, self.OnEdit, self.bntEdit)
1438         self.Bind(wx.EVT_BUTTON, self.OnDelete, self.bntDelete)
1439         self.Bind(wx.EVT_BUTTON, self.OnExit, self.bntClose)
1440 
1441         self.Bind(wx.EVT_CLOSE, self.OnExit)
1442 
1443 
1444     def DoLayout(self):
1445         """
1446         Do layout.
1447         """
1448 
1449         # Sizer.
1450         mainSizer = wx.BoxSizer(wx.HORIZONTAL)
1451         textSizer = wx.BoxSizer(wx.VERTICAL)
1452         btnSizer = wx.StaticBoxSizer(self.StaticSizer, wx.VERTICAL)
1453 
1454         # Assign widgets to sizers.
1455 
1456         # textSizer.
1457         textSizer.Add(self.stEmployees, 0, wx.BOTTOM, 5)
1458         textSizer.Add(self.listCtrl, 1, wx.EXPAND)
1459 
1460         # btnSizer.
1461         btnSizer.Add(self.stSearch)
1462         btnSizer.Add(self.txSearch)
1463         btnSizer.Add((5, 5), -1)
1464         btnSizer.Add(self.bntSearch, 0, wx.ALL, 5)
1465         btnSizer.Add((5, 5), -1)
1466         btnSizer.Add(self.bntClear, 0, wx.ALL, 5)
1467         btnSizer.Add((5, 5), -1)
1468         btnSizer.Add(self.bntShowAll, 0, wx.ALL, 5)
1469         btnSizer.Add((5, 5), -1)
1470         btnSizer.Add(self.bntNew, 0, wx.ALL, 5)
1471         btnSizer.Add((5, 5), -1)
1472         btnSizer.Add(self.bntEdit, 0, wx.ALL, 5)
1473         btnSizer.Add((5, 5), -1)
1474         btnSizer.Add(self.bntDelete, 0, wx.ALL, 5)
1475         btnSizer.Add((5, 5), -1)
1476         btnSizer.Add(self.bntClose, 0, wx.ALL, 5)
1477 
1478         # Assign to mainSizer the other sizers.
1479         mainSizer.Add(textSizer, 1, wx.ALL | wx.EXPAND, 10)
1480         mainSizer.Add(btnSizer, 0, wx.ALL, 10)
1481 
1482         # Assign to panel the mainSizer.
1483         self.panel.SetSizer(mainSizer)
1484         mainSizer.Fit(self)
1485         # mainSizer.SetSizeHints(self)
1486 
1487 
1488     def OnLoadData(self):
1489         """
1490         Retrieve data from the database.
1491         """
1492 
1493         global iCount
1494 
1495         # Retrieve employees data from database
1496         # using the module named data.py
1497         strSQL = "SELECT * FROM Employees"
1498 
1499         # The recordset retrieved
1500         rsRecordset = self.con.OnQuery(strSQL)
1501 
1502         return rsRecordset
1503 
1504 
1505     def OnItemActivated(self, event):
1506         """
1507         ...
1508         """
1509 
1510         item = event.GetItem()
1511         iEmployee = item.GetText()
1512 
1513         # Show what was selected in the frame status bar.
1514         self.myStatusBar.SetStatusText("Selected employee = %s " %(iEmployee), 1)
1515 
1516         #------------
1517 
1518         frame = self.GetTopLevelParent()
1519         frame.OnEdit(event)
1520 
1521 
1522     def OnDelete(self, event):
1523         """
1524         ...
1525         """
1526 
1527         global iEmployees
1528 
1529         # It means that we haven't select a record.
1530         if iEmployees ==0:
1531              wx.MessageBox("ATTENTION !\nSelect an employee !",
1532                            AppTitle,
1533                            wx.OK | wx.ICON_INFORMATION)
1534 
1535              return
1536 
1537         else:
1538             # Ask for delete.
1539             sMessage = "Delete selected employee ? %s " %(iEmployees)
1540             dlgAsk = wx.MessageDialog(None,
1541                                       sMessage,
1542                                       AppTitle,
1543                                       wx.YES_NO | wx.ICON_QUESTION)
1544 
1545             retCode = dlgAsk.ShowModal()
1546 
1547             if (retCode == wx.ID_YES):
1548 
1549                 sSQL = "DELETE FROM Employees WHERE EmployeeID = ?"
1550                 self.con.OnQueryParameter(sSQL, iEmployees)
1551 
1552                 self.Freeze()
1553                 self.OnShowAll(self)
1554                 self.Thaw()
1555 
1556                 wx.MessageBox("Delete operation terminate !",
1557                               AppTitle,
1558                               wx.OK | wx.ICON_INFORMATION)
1559 
1560             elif (retCode == wx.ID_NO):
1561                 wx.MessageBox("Delete operation aborted !",
1562                               AppTitle,
1563                               wx.OK | wx.ICON_INFORMATION)
1564 
1565             dlgAsk.Destroy()
1566 
1567 
1568     def OnUpdateList(self):
1569         """
1570         ...
1571         """
1572 
1573         self.OnShowAll(self)
1574 
1575 
1576     def OnShowAll(self, event):
1577         """
1578         ...
1579         """
1580 
1581 
1582         self.Freeze()
1583         wx.BeginBusyCursor()
1584 
1585         sSQL = "SELECT * FROM Employees WHERE EmployeeID LIKE ? "
1586         sSearch = "%"
1587 
1588         self.OnRetrieveData(sSQL, sSearch)
1589 
1590         self.myStatusBar.SetStatusText("Python-it.org.", 1)
1591 
1592         self.OnClear(self)
1593         self.Thaw()
1594 
1595         wx.CallAfter(wx.EndBusyCursor)
1596 
1597 
1598     def OnClear(self, event):
1599         """
1600         ...
1601         """
1602 
1603         global iEmployees
1604 
1605         sSQL = "SELECT * FROM Employees WHERE EmployeeID LIKE ? "
1606         sSearch = "%"
1607 
1608         self.OnRetrieveData(sSQL, sSearch)
1609 
1610         self.txSearch.Clear()
1611         self.txSearch.SetFocus()
1612 
1613         iEmployees = 0
1614 
1615 
1616     def OnSearch(self, event):
1617         """
1618         ...
1619         """
1620 
1621         # Retrieve what we have write.
1622         sSearch = str(self.txSearch.GetValue())
1623 
1624         # Remove spaces.
1625         # sSearch = "".join(sSearch.strip().split())
1626 
1627         # Add & symbol to force the search.
1628         sSearch = sSearch+"%"
1629 
1630         # Control if we have write something
1631         # before launch the research.
1632         if sSearch == "%" :
1633                 wx.MessageBox("ATTENTION !\nThe search text is empty !",
1634                               AppTitle,
1635                               wx.OK | wx.ICON_INFORMATION)
1636 
1637                 self.txSearch.SetFocus()
1638 
1639                 return
1640 
1641         else:
1642             sSQL = "SELECT * FROM Employees WHERE Surname LIKE ? "
1643             self.OnRetrieveData(sSQL, sSearch)
1644 
1645 
1646     def OnRetrieveData(self, sSQL, IDParameter):
1647         """
1648         ...
1649         """
1650 
1651         global iEmployees
1652 
1653         self.Freeze()
1654 
1655         # Delete the item from listctrl.
1656         self.listCtrl.SetFocus()
1657         self.listCtrl.DeleteAllItems()
1658 
1659         # Retrieve the products recordset from data module.
1660         self.employeeData = self.con.OnQueryParameter(sSQL, IDParameter)
1661 
1662         # Populate the listctrl.
1663         if self.employeeData:
1664             for i in self.employeeData:
1665                 index = self.listCtrl.InsertItem(self.listCtrl.GetItemCount(),
1666                                                  ((str(i[0]))))
1667                 self.listCtrl.SetItem(index, 1, i[1])
1668                 self.listCtrl.SetItem(index, 2, i[2])
1669                 self.listCtrl.SetItem(index, 3, i[3])
1670                 self.listCtrl.SetItem(index, 4, i[4])
1671 
1672                 # Alternate the row colors of a ListCtrl.
1673                 # Mike Driscoll... thank you !
1674                 if index % 2:
1675                     self.listCtrl.SetItemBackgroundColour(index, "#ffffff")
1676                 else:
1677                     self.listCtrl.SetItemBackgroundColour(index, "#ece9d8")   #ecf3fd
1678 
1679         else:
1680              wx.MessageBox("ATTENTION !\nNo results for your research criteria.\nTry with another criteria !",
1681                            AppTitle,
1682                            wx.OK | wx.ICON_INFORMATION)
1683 
1684         self.Thaw()
1685 
1686 
1687     def OnEdit(self, event):
1688         """
1689         ...
1690         """
1691 
1692         global iEmployees
1693 
1694         # It means that we haven't select an employee.
1695         if iEmployees ==0:
1696              wx.MessageBox("ATTENTION !\nSelect an employee !",
1697                            AppTitle,
1698                            wx.OK | wx.ICON_INFORMATION)
1699 
1700              return
1701 
1702         else:
1703             sSQL = "SELECT * FROM Employees WHERE EmployeeID = ?"
1704             self.OnOpenEdit(sSQL, iEmployees)
1705 
1706 
1707     def OnNew(self, event):
1708         """
1709         ...
1710         """
1711 
1712         # Create an instance of the Child_Frame.
1713         self.dlgInsert = self.dlgInsert = MyInsertDlg(caller_dlgInsert=self)
1714 
1715         sTitle = "Insert new employee"
1716         self.dlgInsert.SetTitle(sTitle)
1717         self.dlgInsert.CenterOnParent(wx.BOTH)
1718         self.dlgInsert.ShowModal()
1719         self.dlgInsert.Destroy()
1720 
1721 
1722     def OnOpenEdit(self, sSQL, sParameter):
1723         """
1724         ...
1725         """
1726 
1727         # Retrieve data for the selected product.
1728         rsEmployees = self.con.OnQueryParameter(sSQL, sParameter)
1729 
1730         # Create an instance of the Child_Frame.
1731         self.dlgEdit = self.dlgEdit = MyUpdateDlg(caller_dlgUpdate=self)
1732 
1733         # Populate the fields of the frame with the recordset.
1734         for i in rsEmployees:
1735             self.dlgEdit.txEmployeeID.SetValue(str(i[0]))
1736             self.dlgEdit.txSurname.SetValue(str(i[1]))
1737             self.dlgEdit.txFirstname.SetValue(str(i[2]))
1738             self.dlgEdit.txPhone1.SetValue(str(i[3][0:2]))
1739             self.dlgEdit.txPhone2.SetValue(str(i[3][3:5]))
1740             self.dlgEdit.txPhone3.SetValue(str(i[3][6:8]))
1741             self.dlgEdit.txPhone4.SetValue(str(i[3][9:11]))
1742             self.dlgEdit.txPhone5.SetValue(str(i[3][12:14]))
1743             self.dlgEdit.txEmail.SetValue(str(i[4]))
1744 
1745             # We use this for the title of the frame.
1746             sEmployees =(str(i[1]))
1747 
1748         sTitle = "Select employee : %s" %(sEmployees)
1749         self.dlgEdit.SetTitle(sTitle)
1750         self.dlgEdit.CenterOnParent(wx.BOTH)
1751         self.dlgEdit.ShowModal()
1752         self.dlgEdit.Destroy()
1753 
1754 
1755     def OnUpperCaseText(self, event):
1756         """
1757         ...
1758         """
1759 
1760         # Write in upper mode on widgets the call it.
1761         # Adapted from a script of David Hughes
1762         # Forestfield Software.
1763         # Retrive the widget.
1764         wdgControl = event.GetEventObject()
1765 
1766         # Retrive what we have write.
1767         retValue = wdgControl.GetValue()
1768 
1769         # Upper it.
1770         upValue = retValue.upper()
1771 
1772         if retValue != upValue:
1773             wdgControl.SetValue(upValue)
1774             # Insert it at the end.
1775             wdgControl.SetInsertionPointEnd()
1776 
1777         event.Skip()
1778 
1779 
1780     def OnItemSelected(self, event):
1781         """
1782         ...
1783         """
1784 
1785         global iEmployees
1786 
1787         item = event.GetItem()
1788         iEmployees = item.GetText()
1789 
1790         # Show what was selected in the frame status bar.
1791         self.myStatusBar.SetStatusText("Selected employee = %s " %(iEmployees), 1)
1792 
1793 
1794     def OnColBeginDrag(self, event):
1795         """
1796         Show how to not allow a column to be resized.
1797         """
1798 
1799         if event.GetColumn() == 0:
1800             event.Veto()
1801 
1802 
1803     def OnOpenWidgetInspector(self, event):
1804         """
1805         Activate the widget inspection tool,
1806         giving it a widget to preselect in the tree.
1807         Use either the one under the cursor,
1808         if any, or this frame.
1809         """
1810 
1811         from wx.lib.inspection import InspectionTool
1812         wnd = wx.FindWindowAtPointer()
1813         if not wnd:
1814             wnd = self
1815         InspectionTool().Show(wnd, True)
1816 
1817 
1818     def OnAbout(self, event):
1819         """
1820         ...
1821         """
1822 
1823         # Returns SQLite version.
1824         sSQLversion = self.con.OnSqliteVersion()
1825 
1826         message = """Python-it.org\n
1827                      How to use a wx.ListCtrl.
1828                      Developed in wxPython by Guldo and Beppe.\n
1829                      Improved and updated for wxPython Phoenix by Ecco :-)\n
1830                      Have fun ! ---> Amusez-vous bien !\n
1831                      SQLite version : %s""" %(sSQLversion)
1832 
1833         wx.MessageBox(message,
1834                       AppTitle,
1835                       wx.OK)
1836 
1837 
1838     def OnClose(self):
1839         """
1840         ...
1841         """
1842 
1843         ret = wx.MessageBox("Do you want exit ?",
1844                             AppTitle,
1845                             wx.YES_NO |wx.ICON_QUESTION|
1846                             wx.CENTRE |wx.NO_DEFAULT)
1847 
1848         return ret
1849 
1850 
1851     def OnExit(self, event):
1852         """
1853         ...
1854         """
1855 
1856         # Ask for exit.
1857         intChoice = self.OnClose()
1858 
1859         if intChoice == 2:
1860             # Disconnect from server.
1861             self.con.OnCloseDb()
1862             self.Destroy()
1863 
1864 
1865     def OnTimer(self, event):
1866         """
1867         ...
1868         """
1869 
1870         t = time.localtime(time.time())
1871         sbTime = time.strftime("Astral date %d/%m/%Y are %H:%M:%S", t)
1872         self.myStatusBar.SetStatusText(sbTime, 0)
1873 
1874 #-------------------------------------------------------------------------------
1875 
1876 class MyApp(wx.App, wx.lib.mixins.inspection.InspectionMixin):
1877     """
1878     Thanks to Andrea Gavana.
1879     """
1880     def OnInit(self):
1881 
1882         #------------
1883 
1884         self.locale = wx.Locale(wx.LANGUAGE_ENGLISH)
1885 
1886         #------------
1887 
1888         self.SetAppName("List of employees (v0.0.2)")
1889 
1890         #------------
1891 
1892         self.installDir = os.path.split(os.path.abspath(sys.argv[0]))[0]
1893 
1894         #------------
1895 
1896         # Simplified init method.
1897         # For the InspectionMixin base class.
1898         self.InitInspection()
1899 
1900         #------------
1901 
1902         frame = MyMainFrame(None, -1, title="")
1903         frame.SetSize(800, 527)
1904         self.SetTopWindow(frame)
1905         frame.Center()
1906         frame.Show(True)
1907 
1908         return True
1909 
1910     #---------------------------------------------------------------------------
1911 
1912     def GetInstallDir(self):
1913         """
1914         Returns the installation directory for my application.
1915         """
1916 
1917         return self.installDir
1918 
1919 
1920     def GetIconsDir(self):
1921         """
1922         Returns the icons directory for my application.
1923         """
1924 
1925         icons_dir = os.path.join(self.installDir, "icons")
1926         return icons_dir
1927 
1928 
1929     def GetBitmapsDir(self):
1930         """
1931         Returns the bitmaps directory for my application.
1932         """
1933 
1934         bitmaps_dir = os.path.join(self.installDir, "bitmaps")
1935         return bitmaps_dir
1936 
1937 
1938     def GetDatabaseDir(self):
1939         """
1940         Returns the database directory for my application.
1941         """
1942 
1943         if not os.path.exists("data"):
1944             # Create the data folder, it still doesn't exist.
1945             os.makedirs("data")
1946 
1947         database_dir = os.path.join(self.installDir, "data")
1948         return database_dir
1949 
1950 #-------------------------------------------------------------------------------
1951 
1952 def main():
1953     app = MyApp(redirect=False)
1954     app.MainLoop()
1955 
1956 #-------------------------------------------------------------------------------
1957 
1958 if __name__ == "__main__" :
1959     main()


Download source

source.zip


Additional Information

Link :

http://wxpython-users.1045709.n5.nabble.com/Example-of-Database-Interaction-td2361801.html

http://www.kitebird.com/articles/pydbapi.html

https://dabodev.com/

https://www.pgadmin.org/download/

https://github.com/1966bc/pyggybank

https://sourceforge.net/projects/pyggybank/

- - - - -

https://wiki.wxpython.org/TitleIndex

https://docs.wxpython.org/


Thanks to

Beppe and Guldo (sample_one.py coding), Giuseppe Costanzi, Python-it.org, Robin Dunn, Andrea Gavana, Mike Driscoll, Cody Precord, David Hughes and the wxPython community...


About this page

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

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


Comments

- blah, blah, blah...

List control and SQLite database (Phoenix) (last edited 2019-08-31 13:52:17 by Ecco)

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