1 # sample_one.py
   2 
   3 """
   4 Created  : Tue Jul 10 13:55:00 2012
   5 Author   : Jak_o_Shadows
   6 Link     : http://jak-o-shadows.users.sourceforge.net/python/wxpy/dblistctrl.html
   7 Licensed : Under the GPL general public license v3.
   8 Updated  : For wxPython Phoenix - 17/08/2019 (d/m/y) :-)
   9 """
  10 
  11 import sqlite3
  12 import random
  13 import wx
  14 import wx.lib.mixins.listctrl as listmix
  15 
  16 # class MyFrame
  17 # class MyApp
  18 
  19 #---------------------------------------------------------------------------
  20 
  21 class MyFrame(wx.Frame, listmix.ListCtrlAutoWidthMixin):
  22     def __init__(self, *args, **kwds):
  23         kwds["style"] = wx.DEFAULT_FRAME_STYLE
  24         wx.Frame.__init__(self, *args, **kwds)
  25 
  26         #------------
  27 
  28         # For allowing sorting in descending order.
  29         self.oldC = -1
  30         self.reverse = False
  31         self.command = "SELECT * FROM tbl"
  32 
  33         #------------
  34 
  35         # Simplified init method.
  36         self.SetProperties()
  37         self.CreateCtrls()
  38         self.SetupDataBase()   # Populate the database.
  39         self.BindEvents()
  40         self.DoLayout()
  41 
  42     #-----------------------------------------------------------------------
  43 
  44     def SetProperties(self):
  45         """
  46         ...
  47         """
  48 
  49         self.SetTitle("Sample one (fill a list control with a database)")
  50         self.SetIcon(wx.Icon('wxwin.ico'))
  51 
  52 
  53     def CreateCtrls(self):
  54         """
  55         ...
  56         """
  57 
  58         self.listCtrl = wx.ListCtrl(self, -1,
  59                                     style=wx.LC_REPORT
  60                                        #| wx.BORDER_SUNKEN
  61                                        #| wx.LC_SORT_ASCENDING
  62                                        #| wx.LC_NO_HEADER
  63                                        | wx.LC_VRULES
  64                                        | wx.LC_HRULES
  65                                        | wx.LC_SINGLE_SEL
  66                                        )
  67 
  68         self.listCtrl.SetBackgroundColour("#ccff9c")
  69 
  70         self.listCtrl.InsertColumn(0, "Column - A")
  71         self.listCtrl.InsertColumn(1, "Column - B")
  72         self.listCtrl.InsertColumn(2, "Column - C")
  73 
  74         self.listCtrl.SetColumnWidth(0, 200)
  75         self.listCtrl.SetColumnWidth(1, 200)
  76         self.listCtrl.SetColumnWidth(2, 200)
  77 
  78 
  79     def SetupDataBase(self):
  80         """
  81         Open the database, add a table.
  82         """
  83 
  84         # Open a connection to a DataBase in RAM.
  85         self.con = sqlite3.connect(":memory:")
  86 
  87         command = "CREATE TABLE tbl(id INTEGER PRIMARY KEY AUTOINCREMENT,\
  88                                     a INT,\
  89                                     b TEXT,\
  90                                     c REAL)"
  91 
  92         # Add a table (tbl) with 4 columns).
  93         self.con.execute(command)
  94         self.con.commit()
  95 
  96         self.FillDataBase()
  97 
  98 
  99     def BindEvents(self):
 100         """
 101         Bind some events to an events handle.
 102         """
 103 
 104         self.Bind(wx.EVT_LIST_COL_CLICK, self.SetupSort, self.listCtrl)
 105 
 106 
 107     def DoLayout(self):
 108         """
 109         ...
 110         """
 111 
 112         sizer = wx.BoxSizer(wx.VERTICAL)
 113 
 114         sizer.Add(self.listCtrl, 1, wx.EXPAND, 0)
 115 
 116         self.SetSizer(sizer)
 117         sizer.Fit(self)
 118         self.Layout()
 119 
 120 
 121     def SetupSort(self, event):
 122         """
 123         Sets the command for filling the list control,
 124         based on what column is clicked.
 125         """
 126 
 127         # Get the column that was clicked on.
 128         c = event.GetColumn()
 129 
 130         if c == 0:
 131             # Order by first column.
 132             self.command = "SELECT * FROM tbl ORDER BY a"
 133         elif c == 1:
 134             # Order by second column.
 135             self.command = "SELECT * FROM tbl ORDER BY b"
 136         elif c == 2:
 137             # Order by third column.
 138             self.command = "SELECT * FROM tbl ORDER BY c"
 139 
 140         #------------
 141 
 142         # Toggle reverse.
 143         if c == self.oldC:
 144             self.reverse = not self.reverse
 145         else:
 146             self.reverse = False
 147 
 148         # If reverse, append "DESC" to the select command.
 149         if self.reverse:
 150             self.command += " DESC"
 151 
 152         self.oldC = c
 153         self.FillLC()
 154         event.Skip()
 155 
 156 
 157     def FillDataBase(self):
 158         """
 159         ...
 160         """
 161 
 162         letters = "abcdefghijklmnopqrstuvwxyz"
 163         command = "INSERT INTO tbl VALUES(Null, ?, ?, ?)"
 164 
 165         for i in range(100):
 166             a = random.random()  # Add a random float from (0,26).
 167             b = random.random()  # Add a random float from (0,26).
 168             c = random.random()  # Add a random float from (0,26).
 169             # Add the data to the DataBase.
 170             self.con.execute(command, (a, b, c))
 171 
 172         # Use executemany.
 173         data = []
 174 
 175         for i in range(int(1e2)):
 176             a = random.random()   # Add a random float from (0,26).
 177             b = random.random()   # Add a random float from (0,26).
 178             c = random.random()   # Add a random float from (0,26).
 179             # Add the data to a list.
 180             data.append((a, b, c))
 181 
 182         # All data in the list is added to DataBase.
 183         self.con.executemany(command, data)
 184         # Commits the data, saving it.
 185         self.con.commit()
 186         # Update the list control.
 187         self.FillLC()
 188 
 189 
 190     def FillLC(self):
 191         """
 192         Fills the list control based on the sorting command.
 193         """
 194 
 195         # Since we're sorting, must delete all.
 196         self.listCtrl.DeleteAllItems()
 197 
 198         # Then get a list of tuples of all the data.
 199         data = self.con.execute(self.command).fetchall()
 200 
 201         index = 0
 202         for i in data:
 203             # Loop through and add it.
 204             self.listCtrl.Append(i[1:])
 205 
 206             if index % 2:
 207                 self.listCtrl.SetItemBackgroundColour(index, "white")
 208             else:
 209                 self.listCtrl.SetItemBackgroundColour(index, "#ccff9c")
 210             index += 1
 211 
 212 #---------------------------------------------------------------------------
 213 
 214 class MyApp(wx.App):
 215     def OnInit(self):
 216 
 217         #------------
 218 
 219         wx.InitAllImageHandlers()
 220 
 221         #------------
 222 
 223         frame = MyFrame(None, -1, "")
 224         frame.SetSize(638, 350)
 225         self.SetTopWindow(frame)
 226         frame.Show(True)
 227 
 228         return True
 229 
 230 #-------------------------------------------------------------------------------
 231 
 232 def main():
 233     app = MyApp(False)
 234     app.MainLoop()
 235 
 236 #-------------------------------------------------------------------------------
 237 
 238 if __name__ == "__main__" :
 239     main()

   1 # sample_two.py
   2 
   3 import sys
   4 import os
   5 import platform
   6 import sqlite3
   7 from   random import sample
   8 from   string import ascii_letters
   9 import wx
  10 import wx.lib.mixins.listctrl as listmix
  11 
  12 app_dir = os.path.split(os.path.abspath(sys.argv[0]))[0]
  13 database_dir = os.path.join(app_dir, "data")
  14 dbFile = os.path.join(database_dir, "Database.db3")     # dbFile
  15 
  16 # def create_db
  17 # class MyConnection
  18 # class MyVirtualList
  19 # class MyFrame
  20 # class MyApp
  21 
  22 #-------------------------------------------------------------------------------
  23 
  24 def create_db():
  25     """
  26     Creer une base de donnees de test,
  27     et la remplir avec des donnees aleatoires.
  28     """
  29 
  30     #------------
  31 
  32     # Create/connect to a permanent file database.
  33     con = sqlite3.connect(dbFile, isolation_level=None,    # dbFile
  34                           detect_types=sqlite3.PARSE_DECLTYPES)
  35 
  36     # Establish the cursor, needed to execute the connected db.
  37     cur = con.cursor()
  38 
  39     #------------
  40 
  41     try:
  42         # Create/execute a table (ex : Database) :
  43         cur.execute("""CREATE TABLE Database (Ids INTEGER PRIMARY KEY,
  44                                               Name TEXT,
  45                                               Surname TEXT,
  46                                               Age TEXT,
  47                                               Email TEXT,
  48                                               Phone TEXT)""")
  49     except sqlite3.OperationalError:
  50         print ("Impossible de creer la base de donnees "
  51                "(peut-etre parce qu'elle existe deja ?)")
  52 
  53         return
  54 
  55     #------------
  56 
  57     # Important : if you make changes to the database commits
  58     # current data to the db file (data is persistant now).
  59     con.commit()
  60 
  61     sql = """INSERT INTO Database (Name,
  62                                    Surname,
  63                                    Age,
  64                                    Email,
  65                                    Phone)
  66                            VALUES (?, ?, ?, ?, ?)"""
  67 
  68     cur.execute("BEGIN")
  69 
  70     #------------
  71 
  72     for i in range(600000):
  73         cur.execute(sql, ["".join(sample(ascii_letters, 10))]*5)
  74     con.commit()
  75 
  76 #-------------------------------------------------------------------------------
  77 
  78 class MyConnection(object):
  79     """
  80     Maintient une connexion avec la base de donnees.
  81     """
  82     def __init__(self):
  83 
  84         #------------
  85 
  86         # Return database folder.
  87         self.database_dir = wx.GetApp().GetDatabaseDir()
  88 
  89         # Loading "Database" file.
  90         dbFile = os.path.join(self.database_dir, "Database.db3")
  91 
  92         #------------
  93 
  94         # Create/connect to a permanent file database.
  95         self.con = sqlite3.connect(dbFile, isolation_level=None,    # dbFile
  96                                    detect_types=sqlite3.PARSE_DECLTYPES)
  97 
  98         # Establish the cursor, needed to execute the connected db.
  99         self.cur = self.con.cursor()
 100 
 101     #---------------------------------------------------------------------------
 102 
 103     def OnNumberRows(self):
 104         """
 105         Retourne le nombre de lignes dans la table.
 106         """
 107 
 108         self.cur.execute("SELECT count(Name) FROM Database")
 109 
 110         return self.cur.fetchall()[0][0]
 111 
 112 
 113     def OnOneRow(self, qui):
 114         """
 115         Retourne une ligne de la table.
 116         """
 117 
 118         sql = "SELECT * FROM Database WHERE Ids=?"
 119         self.cur.execute(sql, (qui,))
 120 
 121         return self.cur.fetchall()[0]
 122 
 123 
 124     def OnAll(self):
 125         """
 126         Renvoie toutes les donnees de la table en meme temps.
 127         """
 128 
 129         self.cur.execute("SELECT * from Database")
 130 
 131         while True:
 132             ligne = self.cur.fetchone()
 133             if not ligne:
 134                 raise StopIteration
 135             yield ligne
 136 
 137 #-------------------------------------------------------------------------------
 138 
 139 class MyVirtualList(wx.ListCtrl, listmix.ListCtrlAutoWidthMixin):
 140     """
 141     Creer une ListCtrl virtuelle.
 142     """
 143     def __init__(self, parent):
 144         wx.ListCtrl.__init__(self, parent, -1,
 145                              style=wx.LC_REPORT|wx.LC_VIRTUAL|
 146                              wx.LC_HRULES|wx.LC_VRULES)
 147 
 148         #------------
 149 
 150         # Reference de la base de donnees.
 151         self.con = self.GetParent().con
 152 
 153         #------------
 154 
 155         # Je dois dire combien de row la liste doit avoir.
 156         self.SetItemCount(self.con.OnNumberRows())
 157 
 158         #------------
 159 
 160         self.attr1 = wx.ItemAttr()
 161         self.attr1.SetBackgroundColour("#fbefff")  #fefea7
 162 
 163         #------------
 164 
 165         # mixins.
 166         listmix.ListCtrlAutoWidthMixin.__init__(self)
 167 
 168     #---------------------------------------------------------------------------
 169 
 170     def OnGetItemText(self, row, col):
 171         """
 172         Retour d'un article "c" de la ligne "r".
 173         C'est la magie de la virtualisation.
 174         """
 175 
 176         # Parce que le premier ID de la table et 1.
 177         data = self.con.OnOneRow(row+1)
 178 
 179         return str(data[col])
 180 
 181 
 182     def OnGetItemAttr(self, row):
 183         """
 184         ???.
 185         """
 186 
 187         data = self.con.OnOneRow(row+1)
 188 
 189         index = data[0]
 190         if index % 2:
 191             return self.attr1
 192 
 193         else:
 194             return None
 195 
 196 #-------------------------------------------------------------------------------
 197 
 198 class MyFrame(wx.Frame):
 199     """
 200     Creer une frame principale.
 201     """
 202     def __init__(self, *args, **kwds):
 203         wx.Frame.__init__(self, *args, **kwds)
 204 
 205         #------------
 206 
 207         # Return icons folder.
 208         self.icons_dir = wx.GetApp().GetIconsDir()
 209 
 210         #------------
 211 
 212         # Simplified init method.
 213         self.SetProperties()
 214         self.CreateCtrls()
 215         self.BindEvents()
 216         self.DoLayout()
 217 
 218         #------------
 219 
 220         self.CenterOnScreen(wx.BOTH)
 221 
 222     #---------------------------------------------------------------------------
 223 
 224     def SetProperties(self):
 225         """
 226         ???.
 227         """
 228 
 229         frameicon = wx.Icon(os.path.join(self.icons_dir,
 230                                          "wxwin.ico"),
 231                             type=wx.BITMAP_TYPE_ICO)
 232 
 233         self.SetIcon(frameicon)
 234         self.SetTitle("Sample_two")
 235         self.SetSize((700, 500))
 236         self.SetTransparent(255)
 237         self.SetBackgroundColour("white")
 238 
 239 
 240     def CreateCtrls(self):
 241         """
 242         ???.
 243         """
 244 
 245         self.panel = wx.Panel(self)
 246 
 247         #------------
 248 
 249         self.ConnectDb()
 250 
 251         #------------
 252 
 253         self.search = wx.TextCtrl(self.panel, -1, "")
 254         self.btn = wx.Button(self.panel, -1, "Search")
 255 
 256         #------------
 257 
 258         self.list = MyVirtualList(self.panel)
 259 
 260         for n, c in enumerate(("A", "B", "C",
 261                                "D", "E", "F")):
 262             self.list.InsertColumn(n, c)
 263             self.list.SetColumnWidth(n, 100)
 264 
 265 
 266     def ConnectDb(self):
 267         """
 268         ???.
 269         """
 270 
 271         self.panel.con = MyConnection()
 272 
 273 
 274     def BindEvents(self):
 275         """
 276         Bind some events to an events handle.
 277         """
 278 
 279         self.btn.Bind(wx.EVT_BUTTON, self.OnSearch, self.btn)
 280 
 281 
 282     def DoLayout(self):
 283         """
 284         ???.
 285         """
 286 
 287         s1 = wx.BoxSizer(wx.VERTICAL)
 288         s2 = wx.BoxSizer(wx.HORIZONTAL)
 289 
 290         #------------
 291 
 292         s1.Add(self.list, 1, wx.ALL|wx.EXPAND, 5)
 293         s2.Add(self.search, 1, wx.ALL|wx.EXPAND, 5)
 294         s2.Add(self.btn, 1, wx.ALL|wx.EXPAND, 5)
 295         s1.Add(s2, 0, wx.ALL|wx.EXPAND, 5)
 296 
 297         #------------
 298 
 299         self.panel.SetSizer(s1)
 300 
 301 
 302     def OnSearch(self,event):
 303         """
 304         ???.
 305         """
 306 
 307         SearchText = self.search.GetValue()
 308         print ("Search text : "), SearchText
 309 
 310 #-------------------------------------------------------------------------------
 311 
 312 class MyApp(wx.App):
 313     """
 314     ???.
 315     """
 316     def OnInit(self):
 317 
 318         #------------
 319 
 320         self.SetAppName("Sample_two")
 321 
 322         #------------
 323 
 324         self.installDir = os.path.split(os.path.abspath(sys.argv[0]))[0]
 325 
 326         #------------
 327 
 328         frame = MyFrame(None, -1, "")
 329         self.SetTopWindow(frame)
 330         frame.Show(True)
 331 
 332         return True
 333 
 334     #---------------------------------------------------------------------------
 335 
 336     def GetInstallDir(self):
 337         """
 338         Returns the installation directory for my application.
 339         """
 340 
 341         return self.installDir
 342 
 343 
 344     def GetDatabaseDir(self):
 345         """
 346         Returns the database directory for application.
 347         """
 348 
 349         database_dir = os.path.join(self.installDir, "data")
 350         return database_dir
 351 
 352 
 353     def GetIconsDir(self):
 354         """
 355         Returns the icons directory for my application.
 356         """
 357 
 358         icons_dir = os.path.join(self.installDir, "icons")
 359         return icons_dir
 360 
 361 #-------------------------------------------------------------------------------
 362 
 363 def main():
 364     app = MyApp(redirect=False)
 365     # Demarrage du gestionnaire d'evenements (boucle principale).
 366     app.MainLoop()
 367 
 368 #-------------------------------------------------------------------------------
 369 
 370 if __name__ == "__main__" :
 371     print ("Je vais creer la base de donnees .....")
 372     create_db()
 373     main()