# -*- coding: utf-8 -*- """ Created on Tue Jul 10 13:55:00 2012 @author: Jak_o_Shadows """ import sqlite3 import random import wx class MyFrame(wx.Frame): def __init__(self, *args, **kwds): kwds["style"] = wx.DEFAULT_FRAME_STYLE wx.Frame.__init__(self, *args, **kwds) self.listCtrl = wx.ListCtrl(self, -1, style=wx.LC_REPORT|wx.SUNKEN_BORDER) self.listCtrl.InsertColumn(0, "a") self.listCtrl.InsertColumn(1, "b") self.listCtrl.InsertColumn(2, "c") self.__set_properties() self.__do_layout() self.Bind(wx.EVT_LIST_COL_CLICK, self.setupSort, self.listCtrl) #for allowing sorting in descending order self.oldC = -1 self.reverse = False self.command = "SELECT * FROM tbl" #populate the database self.setupDB() def __set_properties(self): self.SetTitle("Fill a list control with a database!") def __do_layout(self): sizer_1 = wx.BoxSizer(wx.VERTICAL) sizer_1.Add(self.listCtrl, 1, wx.EXPAND, 0) self.SetSizer(sizer_1) sizer_1.Fit(self) self.Layout() def setupSort(self, event): """Sets the command for filling the list control, based on what column is clicked """ c = event.GetColumn() #get the column that was clicked on if c==0: #order by first column self.command = "SELECT * FROM tbl ORDER BY a" elif c==1: #order by second column self.command = "SELECT * FROM tbl ORDER BY b" elif c==2: #order by third column self.command = "SELECT * FROM tbl ORDER BY c" #Toggle reverse if c == self.oldC: self.reverse = not self.reverse else: self.reverse = False #if reverse, append "DESC" to the select command if self.reverse: self.command += " DESC" self.oldC = c self.fillLC() event.Skip() def setupDB(self): """Open the database, add a table""" self.con = sqlite3.connect(":memory:") #open a connection to a DB in RAM command = "CREATE TABLE tbl(id INTEGER PRIMARY KEY AUTOINCREMENT, a INT, b TEXT,\ c REAL)" self.con.execute(command) #add a table (tbl) with 4 columns) self.con.commit() self.fillDB() def fillDB(self): letters = "abcdefghijklmnopqrstuvwxyz" command = "INSERT INTO tbl VALUES(Null, ?, ?, ?)" for i in xrange(100): a = random.randint(1,26) #add a random int from 1 to 26 b = letters[random.randrange(0,26)] #add a random letter from letters c = random.random()*26 #add a random float from (0,26) self.con.execute(command, (a, b, c)) #add the data to the DB #use executemany data = [] for i in xrange(long(1e2)): a = random.randint(1,26) #add a random int from 1 to 26 b = letters[random.randrange(0,26)] #add a random letter from letters c = random.random()*26 #add a random float from (0,26) data.append((a, b, c)) #add the data to a list self.con.executemany(command, data) #all data in the list is added to db self.con.commit() #commits the data, saving it self.fillLC() #update the list control def fillLC(self): """Fills the list control based on the sorting command""" self.listCtrl.DeleteAllItems() #since we're sorting, must delete all #then get a list of tuples of all the data data = self.con.execute(self.command).fetchall() for i in data: #loop through and add it self.listCtrl.Append(i[1:]) if __name__ == "__main__": #wxGlade default stuff app = wx.PySimpleApp(0) wx.InitAllImageHandlers() frame_1 = MyFrame(None, -1, "") app.SetTopWindow(frame_1) frame_1.Show() app.MainLoop()
Now lets go through how it works. Starting from the top, we import sqlite3 for our database, random for some data to fill the database with, and wx for our gui obviously. First some variables are initialized. "self.reverse" and "self.oldC" are necccessary to allow for double clicking of the column header to reverse the sort (from ascending to descending, and vice versa). "self.command" is the command that is called to get data from the database. In the function "setupDB", a connection is made to a database that resides in RAM. A table is then created, saved, and then the function "self.fillDB" is called. This function uses the random module to fill the database with an assortment of integers, strings, and floats. An event, "EVT_LIST_COL_CLICK" is bound to the listCtrl, and the function to be called is "self.setupSort". It is also possible to use self.listCtrl.Bind(wx.EVT_LIST_COL_Click, self.setupSort), however wxGlade does that default to that. In self.setupSort, the column that was clicked on was is determined through event.GetColumn. Since we sort via columns, an if/elif block is used to set the command used for fetching data from the database. Further down, by comparing the column the user selected to the one they selected previously, whether to reverse the data is determined. If it is determined that it should be reversed, the SQL keyword "DESC" is appended. When using moderate table sizes, this works well. However, there is a slight amount of lag when using table sizes of above 1e5, through whether this is from the SELECT operation or the list control is uncertain.
Hopefully this will help somebody