Jak_o_Shadows Web

I really need a tagline

Sometimes you want to use a database to fill a ListCtrl. If your data is able to be sorted, this has the lage advantage of handling the sorting for you.
# -*- 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.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
    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)

    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
            self.reverse = False
        #if reverse, append "DESC" to the select command
        if self.reverse:
            self.command += " DESC"
        self.oldC = c
    def setupDB(self):
        """Open the database, add a table"""
        self.con = sqlite3.connect(":memory:")   #open a connection to a DB in RAM
                    c REAL)"
        self.con.execute(command)    #add a table (tbl) with 4 columns)

    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
if __name__ == "__main__":
    #wxGlade default stuff
    app = wx.PySimpleApp(0)
    frame_1 = MyFrame(None, -1, "")
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