List control and SQLite database (Phoenix)

Keywords : ListCtrl, SQLite, Database, Data tables, Load, Connect, Select, Insert, Update, Delete, Sort, Highlighted row, Bitmap, Icon, ListCtrlAutoWidthMixin, ColumnSorterMixin, 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

To know : for a voluminous database, you must use a virtual list control.

   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 MyFrame
  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         # Set the width of the columns (x4).
1141         # Integer, wx.LIST_AUTOSIZE or wx.LIST_AUTOSIZE_USEHEADER.
1142         self.SetColumnWidth(col=0,  width=50)
1143         self.SetColumnWidth(col=1,  width=130)
1144         self.SetColumnWidth(col=2,  width=130)
1145         self.SetColumnWidth(col=3,  width=110)
1146         self.SetColumnWidth(col=4,  width=2000)
1147 
1148 
1149     def SetProperties(self):
1150         """
1151         Set the list control properties (icon, font, size...).
1152         """
1153 
1154         # Font size and style for listCtrl.
1155         fontSize = self.GetFont().GetPointSize()
1156 
1157         # Text attributes for columns title.
1158         # wx.Font(pointSize, family, style, weight, underline, faceName)
1159         if wx.Platform in ["__WXMAC__", "__WXGTK__"]:
1160             boldFont = wx.Font(fontSize-1,
1161                                wx.DEFAULT,
1162                                wx.NORMAL,
1163                                wx.NORMAL,
1164                                False, "")
1165             self.SetForegroundColour("black")
1166             self.SetBackgroundColour("#ece9d8")   #ecf3fd
1167 
1168         else:
1169             boldFont = wx.Font(fontSize,
1170                                wx.DEFAULT,
1171                                wx.NORMAL,
1172                                wx.BOLD,
1173                                False, "")
1174             self.SetForegroundColour("#808080")
1175             self.SetBackgroundColour("#ece9d8")   #ecf3fd
1176 
1177         self.SetFont(boldFont)
1178 
1179 #-------------------------------------------------------------------------------
1180 
1181 class MyFrame(wx.Frame):
1182     """
1183     ...
1184     """
1185     def __init__(self, parent, id, title,
1186                  style=wx.DEFAULT_FRAME_STYLE |
1187                        wx.NO_FULL_REPAINT_ON_RESIZE |
1188                        wx.CLIP_CHILDREN):
1189         super(MyFrame, self).__init__(parent=None,
1190                                           id=-1,
1191                                           title=title,
1192                                           style=style)
1193 
1194         #------------
1195 
1196         # Returns application name.
1197         self.app_name = wx.GetApp().GetAppName()
1198         # Returns bitmaps folder.
1199         self.bitmaps_dir = wx.GetApp().GetBitmapsDir()
1200         # Returns icons folder.
1201         self.icons_dir = wx.GetApp().GetIconsDir()
1202 
1203         #------------
1204 
1205         # Simplified init method.
1206         self.ConnectDb()
1207         self.SetProperties()
1208         self.MakeMenuBar()
1209         self.MakeStatusBar()
1210         self.CreateCtrls()
1211         self.BindEvents()
1212         self.DoLayout()
1213 
1214         #------------
1215 
1216         # Clock.
1217         self.OnTimer(None)
1218 
1219         self.timer = wx.Timer(self)
1220         self.timer.Start(3000)
1221         self.Bind(wx.EVT_TIMER, self.OnTimer)
1222 
1223     #---------------------------------------------------------------------------
1224 
1225     def ConnectDb(self):
1226         """
1227         Connection to the database.
1228         """
1229 
1230         # Instance from Class MyConnection.
1231         self.con = MyConnection()
1232 
1233 
1234     def SetProperties(self):
1235         """
1236         Set the frame properties (title, icon, size...).
1237         """
1238 
1239         # Setting some frame properties.
1240         frameIcon = wx.Icon(os.path.join(self.icons_dir,
1241                                          "icon_wxWidgets.ico"),
1242                             type=wx.BITMAP_TYPE_ICO)
1243         self.SetIcon(frameIcon)
1244 
1245         #------------
1246 
1247         # Frame cursor.
1248         cursorHand = wx.Cursor(os.path.join(self.icons_dir,
1249                                             "hand.cur"),
1250                                type=wx.BITMAP_TYPE_CUR)
1251         self.SetCursor(cursorHand)
1252 
1253         #------------
1254 
1255         self.SetTitle(("%s") % (self.app_name))
1256 
1257 
1258     def MakeMenuBar(self):
1259         """
1260         Create the menu bar for my app.
1261         """
1262 
1263         # Set an icon to the exit/about menu item.
1264         emptyImg = wx.Bitmap(os.path.join(self.bitmaps_dir,
1265                                           "item_empty.png"),
1266                              type=wx.BITMAP_TYPE_PNG)
1267 
1268         exitImg = wx.Bitmap(os.path.join(self.bitmaps_dir,
1269                                          "item_exit.png"),
1270                             type=wx.BITMAP_TYPE_PNG)
1271 
1272         helpImg = wx.Bitmap(os.path.join(self.bitmaps_dir,
1273                                          "item_about.png"),
1274                             type=wx.BITMAP_TYPE_PNG)
1275 
1276         #------------
1277 
1278         # menu.
1279         mnuFile = wx.Menu()
1280         mnuInfo = wx.Menu()
1281 
1282         # mnuFile.
1283         # Show how to put an icon in the menu item.
1284         menuItem1 = wx.MenuItem(mnuFile, -1, "W&IT\tCtrl+Alt+I", "Widget Inspection Tool !")
1285         menuItem1.SetBitmap(emptyImg)
1286         mnuFile.Append(menuItem1)
1287         self.Bind(wx.EVT_MENU, self.OnOpenWidgetInspector, menuItem1)
1288 
1289         # Show how to put an icon in the menu item.
1290         menuItem2 = wx.MenuItem(mnuFile, wx.ID_EXIT, "&Quit\tCtrl+Q", "Close !")
1291         menuItem2.SetBitmap(exitImg)
1292         mnuFile.Append(menuItem2)
1293         self.Bind(wx.EVT_MENU, self.OnExit, menuItem2)
1294 
1295         # mnuInfo.
1296         # Show how to put an icon in the menu item.
1297         menuItem2 = wx.MenuItem(mnuInfo, wx.ID_ABOUT, "A&bout\tCtrl+A", "Info about developers !")
1298         menuItem2.SetBitmap(helpImg)
1299         mnuInfo.Append(menuItem2)
1300         self.Bind(wx.EVT_MENU, self.OnAbout, menuItem2)
1301 
1302         # menuBar.
1303         menubar = wx.MenuBar()
1304 
1305         # Add menu voices.
1306         menubar.Append(mnuFile, "&File")
1307         menubar.Append(mnuInfo, "I&nfo")
1308 
1309         self.SetMenuBar(menubar)
1310 
1311 
1312     def MakeStatusBar(self):
1313         """
1314         Create the status bar for my frame.
1315         """
1316 
1317         # Statusbar.
1318         self.myStatusBar = self.CreateStatusBar(1)
1319         self.myStatusBar.SetFieldsCount(2)
1320         self.myStatusBar.SetStatusWidths([-8, -4])
1321         self.myStatusBar.SetStatusText("", 0)
1322         self.myStatusBar.SetStatusText("Python-it.org.", 1)
1323 
1324 
1325     def CreateCtrls(self):
1326         """
1327         Create some controls for my frame.
1328         """
1329 
1330         # Font style for wx.StaticText.
1331         font = wx.SystemSettings.GetFont(wx.SYS_DEFAULT_GUI_FONT)
1332         font.SetWeight(wx.BOLD)
1333 
1334         #------------
1335 
1336         # Widgets.
1337         self.panel = wx.Panel(self)
1338 
1339         self.stEmployees = wx.StaticText(self.panel, -1, "Employees list :")
1340         self.stEmployees.SetForegroundColour("gray")
1341         self.stEmployees.SetFont(font)
1342 
1343         # Image list.
1344         self.il = wx.ImageList(16, 16, True)
1345 
1346         # Set an icon for the first column.
1347         self.bmp = wx.Bitmap(os.path.join(self.bitmaps_dir,
1348                                           "employee.png"),
1349                              type=wx.BITMAP_TYPE_PNG)
1350 
1351         # Add image to list.
1352         self.img_idx = self.il.Add(self.bmp)
1353 
1354         self.listCtrl = MyListCtrl(self.panel,
1355                                    -1,
1356                                    style=wx.LC_REPORT |
1357                                          wx.LC_SINGLE_SEL |
1358                                          wx.LC_VRULES |
1359                                          wx.BORDER_SUNKEN)
1360 
1361         # Assign the image list to it.
1362         self.listCtrl.SetImageList(self.il, wx.IMAGE_LIST_SMALL)
1363 
1364         # Retrieve data from the database.
1365         self.employeeData = self.OnLoadData()
1366 
1367         # Populate the wx.ListCtrl.
1368         for i in self.employeeData:
1369             index = self.listCtrl.InsertItem(self.listCtrl.GetItemCount(),
1370                                              ((str(i[0]))))
1371             self.listCtrl.SetItem(index, 1, i[1])
1372             self.listCtrl.SetItem(index, 2, i[2])
1373             self.listCtrl.SetItem(index, 3, i[3])
1374             self.listCtrl.SetItem(index, 4, i[4])
1375             self.listCtrl.SetItemImage(self.listCtrl.GetItemCount() - 1,
1376                                        self.img_idx)
1377 
1378             # Alternate the row colors of a ListCtrl.
1379             # Mike Driscoll... thank you !
1380             if index % 2:
1381                 self.listCtrl.SetItemBackgroundColour(index, "#ffffff")
1382             else:
1383                 self.listCtrl.SetItemBackgroundColour(index, "#ece9d8")   #ecf3fd
1384 
1385         self.stSearch = wx.StaticText(self.panel, -1, 'Search "Surname" :')
1386         self.txSearch = wx.TextCtrl(self.panel, -1, "", size=(100, -1))
1387         self.txSearch.SetToolTip("Search employee !")
1388 
1389         self.StaticSizer = wx.StaticBox(self.panel, -1, "Commands :")
1390         self.StaticSizer.SetForegroundColour("red")
1391         self.StaticSizer.SetFont(font)
1392 
1393         self.bntSearch = wx.Button(self.panel, -1, "&Search")
1394         self.bntSearch.SetToolTip("Search employee !")
1395 
1396         self.bntClear = wx.Button(self.panel, -1, "&Clear")
1397         self.bntClear.SetToolTip("Clear the search text !")
1398 
1399         self.bntShowAll = wx.Button(self.panel, -1, "&All")
1400         self.bntShowAll.SetToolTip("Show all !")
1401 
1402         self.bntNew = wx.Button(self.panel, -1, "&Insert")
1403         self.bntNew.SetToolTip("Insert a new employee !")
1404 
1405         self.bntEdit = wx.Button(self.panel, -1, "&Update")
1406         self.bntEdit.SetToolTip("Update selected employee !")
1407 
1408         self.bntDelete = wx.Button(self.panel, -1, "&Delete")
1409         self.bntDelete.SetToolTip("Delete selected employee !")
1410 
1411         self.bntClose = wx.Button(self.panel, -1, "&Quit")
1412         self.bntClose.SetToolTip("Close !")
1413 
1414 
1415     def BindEvents(self):
1416         """
1417         Bind all the events related to my frame.
1418         """
1419 
1420         self.txSearch.Bind(wx.EVT_TEXT, self.OnUpperCaseText)
1421 
1422         # Intercept the click on the wx.ListCtrl.
1423         self.Bind(wx.EVT_LIST_ITEM_SELECTED, self.OnItemSelected, self.listCtrl)
1424         self.Bind(wx.EVT_LIST_COL_BEGIN_DRAG, self.OnColBeginDrag, self.listCtrl)
1425         self.Bind(wx.EVT_LIST_ITEM_ACTIVATED, self.OnItemActivated, self.listCtrl)
1426 
1427         self.Bind(wx.EVT_BUTTON, self.OnSearch, self.bntSearch)
1428         self.Bind(wx.EVT_BUTTON, self.OnClear, self.bntClear)
1429         self.Bind(wx.EVT_BUTTON, self.OnShowAll, self.bntShowAll)
1430         self.Bind(wx.EVT_BUTTON, self.OnNew, self.bntNew)
1431         self.Bind(wx.EVT_BUTTON, self.OnEdit, self.bntEdit)
1432         self.Bind(wx.EVT_BUTTON, self.OnDelete, self.bntDelete)
1433         self.Bind(wx.EVT_BUTTON, self.OnExit, self.bntClose)
1434 
1435         self.Bind(wx.EVT_CLOSE, self.OnExit)
1436 
1437 
1438     def DoLayout(self):
1439         """
1440         Do layout.
1441         """
1442 
1443         # Sizer.
1444         mainSizer = wx.BoxSizer(wx.HORIZONTAL)
1445         textSizer = wx.BoxSizer(wx.VERTICAL)
1446         btnSizer = wx.StaticBoxSizer(self.StaticSizer, wx.VERTICAL)
1447 
1448         # Assign widgets to sizers.
1449 
1450         # textSizer.
1451         textSizer.Add(self.stEmployees, 0, wx.BOTTOM, 5)
1452         textSizer.Add(self.listCtrl, 1, wx.EXPAND)
1453 
1454         # btnSizer.
1455         btnSizer.Add(self.stSearch)
1456         btnSizer.Add(self.txSearch)
1457         btnSizer.Add((5, 5), -1)
1458         btnSizer.Add(self.bntSearch, 0, wx.ALL, 5)
1459         btnSizer.Add((5, 5), -1)
1460         btnSizer.Add(self.bntClear, 0, wx.ALL, 5)
1461         btnSizer.Add((5, 5), -1)
1462         btnSizer.Add(self.bntShowAll, 0, wx.ALL, 5)
1463         btnSizer.Add((5, 5), -1)
1464         btnSizer.Add(self.bntNew, 0, wx.ALL, 5)
1465         btnSizer.Add((5, 5), -1)
1466         btnSizer.Add(self.bntEdit, 0, wx.ALL, 5)
1467         btnSizer.Add((5, 5), -1)
1468         btnSizer.Add(self.bntDelete, 0, wx.ALL, 5)
1469         btnSizer.Add((5, 5), -1)
1470         btnSizer.Add(self.bntClose, 0, wx.ALL, 5)
1471 
1472         # Assign to mainSizer the other sizers.
1473         mainSizer.Add(textSizer, 1, wx.ALL | wx.EXPAND, 10)
1474         mainSizer.Add(btnSizer, 0, wx.ALL, 10)
1475 
1476         # Assign to panel the mainSizer.
1477         self.panel.SetSizer(mainSizer)
1478         mainSizer.Fit(self)
1479         # mainSizer.SetSizeHints(self)
1480 
1481 
1482     def OnLoadData(self):
1483         """
1484         Retrieve data from the database.
1485         """
1486 
1487         global iCount
1488 
1489         # Retrieve employees data from database
1490         # using the module named data.py
1491         strSQL = "SELECT * FROM Employees"
1492 
1493         # The recordset retrieved
1494         rsRecordset = self.con.OnQuery(strSQL)
1495 
1496         return rsRecordset
1497 
1498 
1499     def OnItemActivated(self, event):
1500         """
1501         ...
1502         """
1503 
1504         item = event.GetItem()
1505         iEmployee = item.GetText()
1506 
1507         # Show what was selected in the frame status bar.
1508         self.myStatusBar.SetStatusText("Selected employee = %s " %(iEmployee), 1)
1509 
1510         #------------
1511 
1512         frame = self.GetTopLevelParent()
1513         frame.OnEdit(event)
1514 
1515 
1516     def OnDelete(self, event):
1517         """
1518         ...
1519         """
1520 
1521         global iEmployees
1522 
1523         # It means that we haven't select a record.
1524         if iEmployees ==0:
1525              wx.MessageBox("ATTENTION !\nSelect an employee !",
1526                            AppTitle,
1527                            wx.OK | wx.ICON_INFORMATION)
1528 
1529              return
1530 
1531         else:
1532             # Ask for delete.
1533             sMessage = "Delete selected employee ? %s " %(iEmployees)
1534             dlgAsk = wx.MessageDialog(None,
1535                                       sMessage,
1536                                       AppTitle,
1537                                       wx.YES_NO | wx.ICON_QUESTION)
1538 
1539             retCode = dlgAsk.ShowModal()
1540 
1541             if (retCode == wx.ID_YES):
1542 
1543                 sSQL = "DELETE FROM Employees WHERE EmployeeID = ?"
1544                 self.con.OnQueryParameter(sSQL, iEmployees)
1545 
1546                 self.Freeze()
1547                 self.OnShowAll(self)
1548                 self.Thaw()
1549 
1550                 wx.MessageBox("Delete operation terminate !",
1551                               AppTitle,
1552                               wx.OK | wx.ICON_INFORMATION)
1553 
1554             elif (retCode == wx.ID_NO):
1555                 wx.MessageBox("Delete operation aborted !",
1556                               AppTitle,
1557                               wx.OK | wx.ICON_INFORMATION)
1558 
1559             dlgAsk.Destroy()
1560 
1561 
1562     def OnUpdateList(self):
1563         """
1564         ...
1565         """
1566 
1567         self.OnShowAll(self)
1568 
1569 
1570     def OnShowAll(self, event):
1571         """
1572         ...
1573         """
1574 
1575 
1576         self.Freeze()
1577         wx.BeginBusyCursor()
1578 
1579         sSQL = "SELECT * FROM Employees WHERE EmployeeID LIKE ? "
1580         sSearch = "%"
1581 
1582         self.OnRetrieveData(sSQL, sSearch)
1583 
1584         self.myStatusBar.SetStatusText("Python-it.org.", 1)
1585 
1586         self.OnClear(self)
1587         self.Thaw()
1588 
1589         wx.CallAfter(wx.EndBusyCursor)
1590 
1591 
1592     def OnClear(self, event):
1593         """
1594         ...
1595         """
1596 
1597         global iEmployees
1598 
1599         sSQL = "SELECT * FROM Employees WHERE EmployeeID LIKE ? "
1600         sSearch = "%"
1601 
1602         self.OnRetrieveData(sSQL, sSearch)
1603 
1604         self.txSearch.Clear()
1605         self.txSearch.SetFocus()
1606 
1607         iEmployees = 0
1608 
1609 
1610     def OnSearch(self, event):
1611         """
1612         ...
1613         """
1614 
1615         # Retrieve what we have write.
1616         sSearch = str(self.txSearch.GetValue())
1617 
1618         # Remove spaces.
1619         # sSearch = "".join(sSearch.strip().split())
1620 
1621         # Add & symbol to force the search.
1622         sSearch = sSearch+"%"
1623 
1624         # Control if we have write something
1625         # before launch the research.
1626         if sSearch == "%" :
1627                 wx.MessageBox("ATTENTION !\nThe search text is empty !",
1628                               AppTitle,
1629                               wx.OK | wx.ICON_INFORMATION)
1630 
1631                 self.txSearch.SetFocus()
1632 
1633                 return
1634 
1635         else:
1636             sSQL = "SELECT * FROM Employees WHERE Surname LIKE ? "
1637             self.OnRetrieveData(sSQL, sSearch)
1638 
1639 
1640     def OnRetrieveData(self, sSQL, IDParameter):
1641         """
1642         ...
1643         """
1644 
1645         global iEmployees
1646 
1647         self.Freeze()
1648 
1649         # Delete the item from listctrl.
1650         self.listCtrl.SetFocus()
1651         self.listCtrl.DeleteAllItems()
1652 
1653         # Retrieve the products recordset from data module.
1654         self.employeeData = self.con.OnQueryParameter(sSQL, IDParameter)
1655 
1656         # Populate the listctrl.
1657         if self.employeeData:
1658             for i in self.employeeData:
1659                 index = self.listCtrl.InsertItem(self.listCtrl.GetItemCount(),
1660                                                  ((str(i[0]))))
1661                 self.listCtrl.SetItem(index, 1, i[1])
1662                 self.listCtrl.SetItem(index, 2, i[2])
1663                 self.listCtrl.SetItem(index, 3, i[3])
1664                 self.listCtrl.SetItem(index, 4, i[4])
1665 
1666                 # Alternate the row colors of a ListCtrl.
1667                 # Mike Driscoll... thank you !
1668                 if index % 2:
1669                     self.listCtrl.SetItemBackgroundColour(index, "#ffffff")
1670                 else:
1671                     self.listCtrl.SetItemBackgroundColour(index, "#ece9d8")   #ecf3fd
1672 
1673         else:
1674              wx.MessageBox("ATTENTION !\nNo results for your research criteria.\nTry with another criteria !",
1675                            AppTitle,
1676                            wx.OK | wx.ICON_INFORMATION)
1677 
1678         self.Thaw()
1679 
1680 
1681     def OnEdit(self, event):
1682         """
1683         ...
1684         """
1685 
1686         global iEmployees
1687 
1688         # It means that we haven't select an employee.
1689         if iEmployees ==0:
1690              wx.MessageBox("ATTENTION !\nSelect an employee !",
1691                            AppTitle,
1692                            wx.OK | wx.ICON_INFORMATION)
1693 
1694              return
1695 
1696         else:
1697             sSQL = "SELECT * FROM Employees WHERE EmployeeID = ?"
1698             self.OnOpenEdit(sSQL, iEmployees)
1699 
1700 
1701     def OnNew(self, event):
1702         """
1703         ...
1704         """
1705 
1706         # Create an instance of the Child_Frame.
1707         self.dlgInsert = self.dlgInsert = MyInsertDlg(caller_dlgInsert=self)
1708 
1709         sTitle = "Insert new employee"
1710         self.dlgInsert.SetTitle(sTitle)
1711         self.dlgInsert.CenterOnParent(wx.BOTH)
1712         self.dlgInsert.ShowModal()
1713         self.dlgInsert.Destroy()
1714 
1715 
1716     def OnOpenEdit(self, sSQL, sParameter):
1717         """
1718         ...
1719         """
1720 
1721         # Retrieve data for the selected product.
1722         rsEmployees = self.con.OnQueryParameter(sSQL, sParameter)
1723 
1724         # Create an instance of the Child_Frame.
1725         self.dlgEdit = self.dlgEdit = MyUpdateDlg(caller_dlgUpdate=self)
1726 
1727         # Populate the fields of the frame with the recordset.
1728         for i in rsEmployees:
1729             self.dlgEdit.txEmployeeID.SetValue(str(i[0]))
1730             self.dlgEdit.txSurname.SetValue(str(i[1]))
1731             self.dlgEdit.txFirstname.SetValue(str(i[2]))
1732             self.dlgEdit.txPhone1.SetValue(str(i[3][0:2]))
1733             self.dlgEdit.txPhone2.SetValue(str(i[3][3:5]))
1734             self.dlgEdit.txPhone3.SetValue(str(i[3][6:8]))
1735             self.dlgEdit.txPhone4.SetValue(str(i[3][9:11]))
1736             self.dlgEdit.txPhone5.SetValue(str(i[3][12:14]))
1737             self.dlgEdit.txEmail.SetValue(str(i[4]))
1738 
1739             # We use this for the title of the frame.
1740             sEmployees =(str(i[1]))
1741 
1742         sTitle = "Select employee : %s" %(sEmployees)
1743         self.dlgEdit.SetTitle(sTitle)
1744         self.dlgEdit.CenterOnParent(wx.BOTH)
1745         self.dlgEdit.ShowModal()
1746         self.dlgEdit.Destroy()
1747 
1748 
1749     def OnUpperCaseText(self, event):
1750         """
1751         ...
1752         """
1753 
1754         # Write in upper mode on widgets the call it.
1755         # Adapted from a script of David Hughes
1756         # Forestfield Software.
1757         # Retrive the widget.
1758         wdgControl = event.GetEventObject()
1759 
1760         # Retrive what we have write.
1761         retValue = wdgControl.GetValue()
1762 
1763         # Upper it.
1764         upValue = retValue.upper()
1765 
1766         if retValue != upValue:
1767             wdgControl.SetValue(upValue)
1768             # Insert it at the end.
1769             wdgControl.SetInsertionPointEnd()
1770 
1771         event.Skip()
1772 
1773 
1774     def OnItemSelected(self, event):
1775         """
1776         ...
1777         """
1778 
1779         global iEmployees
1780 
1781         item = event.GetItem()
1782         iEmployees = item.GetText()
1783 
1784         # Show what was selected in the frame status bar.
1785         self.myStatusBar.SetStatusText("Selected employee = %s " %(iEmployees), 1)
1786 
1787 
1788     def OnColBeginDrag(self, event):
1789         """
1790         Show how to not allow a column to be resized.
1791         """
1792 
1793         if event.GetColumn() == 0:
1794             event.Veto()
1795 
1796 
1797     def OnOpenWidgetInspector(self, event):
1798         """
1799         Activate the widget inspection tool,
1800         giving it a widget to preselect in the tree.
1801         Use either the one under the cursor,
1802         if any, or this frame.
1803         """
1804 
1805         from wx.lib.inspection import InspectionTool
1806         wnd = wx.FindWindowAtPointer()
1807         if not wnd:
1808             wnd = self
1809         InspectionTool().Show(wnd, True)
1810 
1811 
1812     def OnAbout(self, event):
1813         """
1814         ...
1815         """
1816 
1817         # Returns SQLite version.
1818         sSQLversion = self.con.OnSqliteVersion()
1819 
1820         message = """Python-it.org\n
1821                      How to use a wx.ListCtrl.
1822                      Developed in wxPython by Guldo and Beppe.\n
1823                      Improved and updated for wxPython Phoenix by Ecco :-)\n
1824                      Have fun ! ---> Amusez-vous bien !\n
1825                      SQLite version : %s""" %(sSQLversion)
1826 
1827         wx.MessageBox(message,
1828                       AppTitle,
1829                       wx.OK)
1830 
1831 
1832     def OnClose(self):
1833         """
1834         ...
1835         """
1836 
1837         ret = wx.MessageBox("Do you want exit ?",
1838                             AppTitle,
1839                             wx.YES_NO |wx.ICON_QUESTION|
1840                             wx.CENTRE |wx.NO_DEFAULT)
1841 
1842         return ret
1843 
1844 
1845     def OnExit(self, event):
1846         """
1847         ...
1848         """
1849 
1850         # Ask for exit.
1851         intChoice = self.OnClose()
1852 
1853         if intChoice == 2:
1854             # Disconnect from server.
1855             self.con.OnCloseDb()
1856             self.Destroy()
1857 
1858 
1859     def OnTimer(self, event):
1860         """
1861         ...
1862         """
1863 
1864         t = time.localtime(time.time())
1865         sbTime = time.strftime("Astral date %d/%m/%Y are %H:%M:%S", t)
1866         self.myStatusBar.SetStatusText(sbTime, 0)
1867 
1868 #-------------------------------------------------------------------------------
1869 
1870 class MyApp(wx.App, wx.lib.mixins.inspection.InspectionMixin):
1871     """
1872     Thanks to Andrea Gavana.
1873     """
1874     def OnInit(self):
1875 
1876         #------------
1877 
1878         self.locale = wx.Locale(wx.LANGUAGE_ENGLISH)
1879 
1880         #------------
1881 
1882         self.SetAppName("List of employees (v0.0.2)")
1883 
1884         #------------
1885 
1886         self.installDir = os.path.split(os.path.abspath(sys.argv[0]))[0]
1887 
1888         #------------
1889 
1890         # Simplified init method.
1891         # For the InspectionMixin base class.
1892         self.InitInspection()
1893 
1894         #------------
1895 
1896         frame = MyFrame(None, -1, title="")
1897         frame.SetSize(800, 527)
1898         self.SetTopWindow(frame)
1899         frame.Center()
1900         frame.Show(True)
1901 
1902         return True
1903 
1904     #---------------------------------------------------------------------------
1905 
1906     def GetInstallDir(self):
1907         """
1908         Returns the installation directory for my application.
1909         """
1910 
1911         return self.installDir
1912 
1913 
1914     def GetIconsDir(self):
1915         """
1916         Returns the icons directory for my application.
1917         """
1918 
1919         icons_dir = os.path.join(self.installDir, "icons")
1920         return icons_dir
1921 
1922 
1923     def GetBitmapsDir(self):
1924         """
1925         Returns the bitmaps directory for my application.
1926         """
1927 
1928         bitmaps_dir = os.path.join(self.installDir, "bitmaps")
1929         return bitmaps_dir
1930 
1931 
1932     def GetDatabaseDir(self):
1933         """
1934         Returns the database directory for my application.
1935         """
1936 
1937         if not os.path.exists("data"):
1938             # Create the data folder, it still doesn't exist.
1939             os.makedirs("data")
1940 
1941         database_dir = os.path.join(self.installDir, "data")
1942         return database_dir
1943 
1944 #-------------------------------------------------------------------------------
1945 
1946 def main():
1947     app = MyApp(redirect=False)
1948     app.MainLoop()
1949 
1950 #-------------------------------------------------------------------------------
1951 
1952 if __name__ == "__main__" :
1953     main()


Second example

[ATTACH]

To know : for a voluminous database, you must use a virtual list control.

   1 # sample_two.py


Download source

source.zip


Additional Information

Link :

https://wiki.wxpython.org/List%20control%20%28Phoenix%29

https://wiki.wxpython.org/List%20control%20virtual%20%28Phoenix%29

https://wiki.wxpython.org/List%20control%20virtual%20and%20SQLite%20database%20%28Phoenix%29

https://wiki.wxpython.org/List%20control%20and%20drag%20and%20drop%20-%20DnD%20%28Phoenix%29#Download_source

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 / Guldo (sample_one.py coding), Giuseppe Costanzi, Python-it.org, Robin Dunn, Robin Rappin, 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 2020-05-18 13:36:47 by Ecco)

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