Fully Functional Demo

When i want to learn something new i like to see a fully functional program which i can tinker with and change. This program is written for wxPython-2.4.2.4 by an amateur, probably full of worst practices! But attempts to show many of the common widgets in action. The wxPython code is in two files and the database access code in a separate file called 'utilities.py'. There are three versions of the utilities file to access a postgresql, firebird or mysql database. Any one wishing to actually run this program could either use this or build his own utilities module using his favourite database or flat files or shelves and pickles or whatever. There has been some debate on the advisability of using a rdbms in a demo. But in the real world this happens. Here the data access functions are all grouped in 'utitlities.py' and it would be a trivial exercise to translate the functions to suit ones favourite database.

This program is a mini address book. You can view and print the address book, add, edit and delete items. The two wxPython files are managepersons.py and addperson.py:

Werner Bruhin has contributed a firebird version of the utilities.py and the sql, which are at the end of the wiki.

Mike Fletcher has written a Py Table version of utilities.py, this should support Py SQLite, Py PgSQL and MySQLdb - the files are posted after Werner's files

I keep correcting the indentation, but it keeps getting jumbled up - sorry!

managepersons.py:

import wx
from addperson import addperson
from utilities import *
from wxPython.lib.printout import PrintTable

ADD=wx.NewId()
EDIT=wx.NewId()
CLOSE=wx.NewId()
DISPLAY=wx.NewId()
DELETE=wx.NewId()
PRINT=wx.NewId()


class managepersons(wx.Dialog):
    def __init__(self,parent,id,title, **kwds):
        # begin wxGlade: manage_users.__init__
        self.idu = ''
        self.title = "Manage Persons"
        self.parent=parent
        kwds["style"] = wx.RESIZE_BORDER
        wx.Dialog.__init__(self, parent,id,title, **kwds)
        
        self.lctsup = wx.ListCtrl(self, DISPLAY,
            style=wx.LC_REPORT|wx.LC_SINGLE_SEL, size=(1,150))

        self.lctsup.InsertColumn(0,'Title')
        self.lctsup.InsertColumn(1,'First Name')
        self.lctsup.InsertColumn(2,'Last Name')
        self.lctsup.InsertColumn(3,'Profession')
        
       
        self.butadd = wx.Button(self, ADD, "Add")
        self.butedit = wx.Button(self, EDIT, "Edit")
        self.butdelete = wx.Button(self, DELETE, "Delete")
        self.butprint = wx.Button(self, PRINT, "Print")
        self.butclose = wx.Button(self, CLOSE, "Close")
        self.refresh()
        self.__do_layout()




    def __do_layout(self):

        boxa = wx.BoxSizer(wx.VERTICAL)
        boxt = wx.BoxSizer(wx.VERTICAL)
        boxb = wx.BoxSizer(wx.HORIZONTAL)
        boxt.Add(self.lctsup, 1, wx.EXPAND)
        boxb.Add(self.butadd, 0, 
         wx.ALIGN_CENTER_HORIZONTAL|wx.ALIGN_CENTER_VERTICAL)
        boxb.Add(self.butedit, 0, 
         wx.ALIGN_CENTER_HORIZONTAL|wx.ALIGN_CENTER_VERTICAL)
        boxb.Add(self.butdelete, 0, 
         wx.ALIGN_CENTER_HORIZONTAL|wx.ALIGN_CENTER_VERTICAL)
        boxb.Add(self.butprint, 0, 
         wx.ALIGN_CENTER_HORIZONTAL|wx.ALIGN_CENTER_VERTICAL)
        boxb.Add(self.butclose, 0, 
         wx.ALIGN_CENTER_HORIZONTAL|wx.ALIGN_CENTER_VERTICAL)
        boxa.Add(boxt,1, wx.EXPAND)
        boxa.Add(boxb,0, wx.EXPAND)
        self.SetAutoLayout(1)
        self.SetSizer(boxa)
        boxa.Fit(self)
        boxa.SetSizeHints(self)
        self.Layout()
        wx.EVT_BUTTON(self, ADD, self.add)
        wx.EVT_BUTTON(self, EDIT, self.edit)
        wx.EVT_BUTTON(self, DELETE, self.delete)
        wx.EVT_LIST_ITEM_SELECTED(self, DISPLAY, self.display)
        wx.EVT_BUTTON(self, CLOSE, self.close)
        wx.EVT_BUTTON(self, PRINT, self.onprint)
 
    def close(self, event):
     self.Destroy()

    def refresh(self):
        self.lctsup.DeleteAllItems()
        x = 0
        self.personList = getpersons() #external program
        for k in self.personList:
            self.lctsup.InsertStringItem(x,k['salutation'])
            self.lctsup.SetStringItem(x,1,k['fname'])
            self.lctsup.SetStringItem(x,2,str(k['lname']))
            self.lctsup.SetStringItem(x,3,str(k['profession']))
            x += 1
            
    def add(self, event):
        aperson = addperson(self, -1, "Add New Person",
                                 False, 0)
    val=aperson.ShowModal()
        if val:
            self.refresh()
            
    def edit(self, event):
        if self.idu:
            aperson = addperson(self, -1, "Edit Person",
                                     True, self.idu)
            val=aperson.ShowModal()
            if val:
                self.refresh()
                
    def delete(self, event):
        if self.idu:
            mg = "Are You Sure You want to delete %s" %(getname(self.idu))
            msg = wx.MessageDialog(self,mg,"Warning",wx.OK|wx.CANCEL)
            res = msg.ShowModal()
            msg.Destroy()
            if res == wx.ID_OK:
                tes = deleteperson(self.idu) #external program
                if tes:
                    #display error message
                    error = wx.MessageDialog(self,"Delete Failed","Error",
                       wx.OK)
                    error.ShowModal()
                    error.Destroy()
                else:
                    self.refresh()
                    
    def display(self,event):
        curitem = event.m_itemIndex
        fitem = self.lctsup.GetItem(curitem,1).GetText()
        litem = self.lctsup.GetItem(curitem,2).GetText()
        self.idu = getid(fitem,litem)
        


    def onprint(self,evt):
        data = []
        data.append(["Salutation","First Name","Last Name","Profession"])
        for k in self.personList:
            data.append([k['salutation'],k['fname'],k['lname'],
         k['profession']])
        prt = PrintTable(self.parent)
        prt.data = data[1:]
        prt.left_margin = .2
        prt.set_column = [ 2,2,2,2]
        prt.label = data[0]
        prt.top_margin = 1
        prt.SetLandscape()
        prt.SetHeader("Person List Report", size = 30)
        prt.SetFooter("Page No", colour = wx.NamedColour('RED'), type ="Num")
        prt.SetRowSpacing(10,10)
        prt.Print()


class app(wx.App):
    def OnInit(self):
        frame=managepersons(None,-1,'')
        self.SetTopWindow(frame)
        frame.Show()
        return 1



if __name__ == "__main__":
    prog = app(0)
    prog.MainLoop()

And this is addperson.py

import wx
from wxPython.lib.rcsizer import RowColSizer
from utilities import *

ADD=wx.NewId()
CANCEL=wx.NewId()


class addperson(wx.Dialog):
    def __init__(self,parent,id,title,edit,idu,**kwds):
        
        #initialisation
        
        self.title = title
        self.edit = edit
        self.idu = 0
        self.parms = {
          'fname':'',
          'lname':'',
          'profession':'',
          'salutation':'',
           }
        salutations = ['Dr','Mr','Ms']
        professions = ['Doctor','Lawyer','Engineer']
        if edit:
            self.parms = getpersonparms(idu)
            self.idu = idu
            
        wx.Dialog.__init__(self,parent,id,title,**kwds)
        
        #create the widgets
        
        self.Salute = wx.RadioBox(self, -1,"Salutation: ",
            wx.DefaultPosition,wx.DefaultSize,salutations,1,wx.RA_SPECIFY_ROWS)
        self.Salute.SetStringSelection(self.parms['salutation'])
        self.labfname = wx.StaticText(self, -1, "First Name:")
        self.Fname = wx.TextCtrl(self, -1, self.parms['fname'])
        self.lablname = wx.StaticText(self, -1, "Last Name:")
        self.Lname = wx.TextCtrl(self, -1, self.parms['lname'])
        self.labprofession = wx.StaticText(self, -1,"Profession:")
        self.Profession = wx.ComboBox(self, -1, choices=professions,
              style=wx.CB_DROPDOWN)
        self.Profession.SetValue(self.parms['profession'])
        self.butsave = wx.Button(self, ADD, "Save")
        self.butcancel = wx.Button(self, CANCEL, "Cancel")
        self.Fname.SetSize((320, 26))
        self.Fname.SetMaxLength(40)
        self.Lname.SetSize((320, 26))
        self.Lname.SetMaxLength(40)

        self.__do_layout()


    def __do_layout(self):
        
        self.SetPosition([300,250])
        boxl = RowColSizer()
        boxl.Add(self.Salute, row=1, col=1, colspan=2)
        boxl.Add(self.labfname, row=2, col=1)
        boxl.Add(self.Fname, row=2, col=2)
        boxl.Add(self.lablname, row=3, col=1)
        boxl.Add(self.Lname, row=3, col=2)
        boxl.Add(self.labprofession, row=4, col=1)
        boxl.Add(self.Profession, row=4, col=2)
       
        
        boxb = wx.BoxSizer(wx.HORIZONTAL)
        boxb.Add(self.butsave, 0, 
         wx.ALIGN_CENTER_HORIZONTAL|wx.ALIGN_CENTER_VERTICAL, 0)
        boxb.Add(50,10,0)
        boxb.Add(self.butcancel, 0, 
         wx.ALIGN_CENTER_HORIZONTAL|wx.ALIGN_CENTER_VERTICAL, 0)
        boxl.Add(boxb, row=5, col=2)
        
        for x in range(1,5):
            boxl.AddSpacer(75,30, pos=(x,1))
            boxl.AddSpacer(380,1, pos=(x,2))
            
        boxl.AddSpacer(75,30,pos=(5,1))
        self.SetAutoLayout(1)
        self.SetSizer(boxl)
        boxl.Fit(self)
        boxl.SetSizeHints(self)
        self.Layout()
        wx.EVT_BUTTON(self, ADD, self.add)
        wx.EVT_BUTTON(self, CANCEL, self.cancel)
 
    def cancel(self,event):
        self.EndModal(0)
        
    def add(self, event):
        ok = True
        msg = ''
        parms = self.parms
        parms['lname'] = self.Lname.GetValue().strip()
        #strip to get rid of leading and trailing spaces
        parms['fname'] = self.Fname.GetValue()
        parms['profession'] = self.Profession.GetValue()
        parms['salutation'] = self.Salute.GetStringSelection()
        
        #check that all fields are filled
        for k,v in parms.items():
            if v == '':
                msg += "Fill in %s" %(k.capitalize())
                ok = False
        #if edit mode
        if self.edit:
            #check for duplicates
                msg,ok = duplicedit(parms['fname'],
                                    parms['lname'],self.idu,msg,ok)
                if ok:
                    updateperson(parms,self.idu) #external program
                    self.EndModal(1)
                else:
                    #display error message
                    error = wx.MessageDialog(self,msg,'Error',wx.OK)
                    error.ShowModal()
                    error.Destroy()    
        #if add mode    
        else:
            msg,ok = duplic(parms['fname'],parms['lname'],msg,ok)
            if ok:
                enterperson(parms) #external program
                self.EndModal(1)
            else:
                error = wx.MessageDialog(self,msg,'Error',wx.OK)
                error.ShowModal()
                error.Destroy()

The database stuff is in utilities.py

import psycopg


def dbi():
    dbi = psycopg.connect("dbname=persdb host=localhost\
    user=postgres password=postgres")
    return dbi


def duplic(fname,lname,msg,ok):
    dup = False
    db = dbi()
    curs = db.cursor()
    curs.execute("select * from persons where fname = '%s'\
                and lname='%s'" % (fname,lname))
    row = curs.fetchone()
    if row:
        ok = False
        msg += "Duplicate Entry: %s" %(item)
    return msg, ok


def duplicedit(fname,lname,id,msg,ok):
    dup = False
    db = dbi()
    curs = db.cursor()
    curs.execute("select * from persons where fname = '%s'\
                and lname='%s' and id != %s" % (fname,lname,id))
    row = curs.fetchone()
    if row:
        ok = False
        msg += "Duplicate Entry: %s" %(item)
    return msg, ok

def getpersonparms(id):
    db = dbi()
    cur = db.cursor()
    cur.execute("select * from persons where id = %s" %(id))
    k = cur.dictfetchone()
    return k

def getpersons():
    db = dbi()
    cur = db.cursor()
    cur.execute("select * from persons order by lname,fname")
    li = cur.dictfetchall()
    return li

def getname(id):
    res = ''
    db = dbi()
    try:
        cur = db.cursor()
        cur.execute("select fname from persons\
                 where id = %s" %(id))
        res = cur.fetchone()[0]
    except:
        pass
    return res

def getid(fname,lname):
    res = ''
    db = dbi()
    cur = db.cursor()
    try:
        cur.execute("select id from persons\
                     where fname = '%s' and lname='%s'" %(fname,lname))
        res = cur.fetchone()[0]
    except:
        pass
    return res

def deleteperson(id):
    con = dbi()
    c = con.cursor()
    delete = 0
    try:
        c.execute("delete from persons\
        where id = %s" % (int(id)))
        con.commit() 
    except:
        delete = 1
    return delete

def updateperson(parms,id):
    con = dbi()
    c = con.cursor()
    c.execute("update persons \
    set lname='%s', fname='%s', salutation='%s',profession='%s'\
     where id = %s"
    % (parms['lname'],parms['fname'],parms['salutation'],
    parms['profession'],id))
    con.commit()

def enterperson(parms):
    con = dbi()
    c = con.cursor()
    c.execute("insert into persons(fname,lname,profession,salutation)\
       values ('%s', '%s', '%s', '%s')" % (parms['fname'],
       parms['lname'],parms['profession'],parms['salutation']))
    con.commit()

SQL schema for database: persons.sql

create table persons(
        id serial unique,
        fname varchar(25) not null,
        lname varchar(25) not null,
        salutation varchar(10) not null,
        profession varchar(25) not null,
        CONSTRAINT personkey primary key(fname,lname)
        );

Firebird version of utilities.py

"""A Firebird (tested on 1.5) version of utilities
"""
import kinterbasdb

updatestmt = '''update persons set fname = ?, lname = ?, profession = ?,
                    salutation = ?
                    where id = ?'''

insertstmt = '''insert into persons(fname, lname, profession, salutation)
                     values (?, ?, ?, ?)'''

def dbi():
    dbi = kinterbasdb.connect(database='C:\Dev\RealWorldSample\persdb.fdb',
                              host='localhost',
                              user='SYSDBA', password='masterkey')
    return dbi


def duplic(fname,lname,msg,ok):
    dup = False
    db = dbi()
    curs = db.cursor()
    curs.execute('''select * from persons where fname = '%s'
                and lname='%s' ''' % (fname,lname))
    row = curs.fetchone()
    if row:
        ok = False
        msg += "Duplicate Entry: %s" %(item)
    return msg, ok


def duplicedit(fname,lname,id,msg,ok):
    dup = False
    db = dbi()
    curs = db.cursor()
    curs.execute('''select * from persons where fname = '%s'
                and lname='%s' and id != %s''' % (fname,lname,id))
    row = curs.fetchone()
    if row:
        ok = False
        #msg += "Duplicate Entry: %s" %(item)
        msg += "Duplicate Entry: %s %s" %(fname, lname)
    return msg, ok
 
def getpersonparms(id):
    db = dbi()
    cur = db.cursor()
    cur.execute("select * from persons where id = %s" %(id))
    k = cur.fetchonemap() 
    return k
 
def getpersons():
    db = dbi() 
    cur = db.cursor()
    cur.execute("select * from persons order by lname,fname") 
    li = cur.fetchallmap()
    return li 

def getname(id): 
    res = ''
    db = dbi() 
    try:
        cur = db.cursor() 
        cur.execute('''select fname from persons
                 where id = %s''' %(id)) 
        res = cur.fetchone()[0]
    except: 
        pass
    return res 

def getid(fname,lname): 
    res = ''
    db = dbi() 
    cur = db.cursor()
    try: 
        cur.execute('''select id from persons
                     where fname = '%s' and lname='%s' ''' %(fname,lname)) 
        res = cur.fetchone()[0]
    except: 
        pass
    return res 

def deleteperson(id): 
    con = dbi()
    c = con.cursor() 
    delete = 0
    try: 
        c.execute('''delete from persons
                  where id = %s''' % (int(id))) 
        con.commit()
    except: 
        delete = 1
    return delete 

def updateperson(parms,id): 
    con = dbi()
    c = con.cursor()
    # coding style 1
    if True == 0:
        c.execute('''update persons
                     set lname='%s', fname='%s', salutation='%s',
                     profession='%s'
                     where id = %s''' 
                     % (parms['lname'],parms['fname'],parms['salutation'],
                        parms['profession'],id)) 

    # coding style 2
    elif True == 0:
        c.execute(updatestmt, (parms['fname'],
                               parms['lname'],
                               parms['profession'],
                               parms['salutation'],
                               id))

    # coding style 3
    elif True == 1:
        parms['id'] = id

        c.execute('''update persons  
                   set lname='%(lname)s', fname='%(fname)s',
                   salutation='%(salutation)s',
                   profession='%(profession)s'
                   where id = %(id)s''' % parms)

    con.commit() 

def enterperson(parms): 
    con = dbi()
    c = con.cursor()
    # coding style 1
    if True == 0:
        c.execute('''insert into persons(fname, lname, profession, salutation)
                         values ('%(fname)s', '%(lname)s', '%(profession)s',
                         '%(salutation)s')'''
                         % parms)

    # coding style 2
    elif True == 0:
        c.execute(insertstmt, (parms['fname'],
                               parms['lname'],
                               parms['profession'],
                               parms['salutation']))

    # coding style 3
    elif True == 1:
        c.execute('''insert into persons (fname, lname, profession, salutation)
                        values ('%(lname)s', '%(fname)s', '%(profession)s',
                        '%(salutation)s')''' % parms)
        
    con.commit()

Firebird version of persons.sql

CREATE GENERATOR GEN_PERSONS_ID;
create table persons( 
        id integer not null, 
        fname varchar(25) not null, 
        lname varchar(25) not null, 
        salutation varchar(10) not null, 
        profession varchar(25) not null, 
        CONSTRAINT personkey primary key(fname,lname) 
        );
CREATE TRIGGER PERSONS_BI0 FOR PERSONS
ACTIVE BEFORE INSERT POSITION 0
AS BEGIN
      IF(NEW."ID" IS NULL) THEN NEW."ID" = GEN_ID("GEN_PERSONS_ID",1);
    END;

== PyTable version of utilities.py

from pytable import dbspecifier, sqlquery
import traceback

# this would normally be parameterised and stored somewhere...
SPECIFIER = dbspecifier.DBSpecifier(
         database = "addressdemo",
         drivername = "PyPgSQL",
)
##SPECIFIER = dbspecifier.DBSpecifier(
##       database = ":memory:",
##       drivername = "SQLite",
##)
# Real PyTable apps use better mechanisms for accessing these...
SCHEMA = None
PERSONS = None # the only table we use
# recreating connections to in-memory databases creates new dbs,
# so for this cross-database case, we'll
CONNECTION = None

def dbi():
         """Get a configured database connection

         Side effect is to ensure that we've got a resolved db schema
         and that the schema's table exists in the database...
         """
         global SCHEMA, PERSONS, CONNECTION
         if CONNECTION is None or CONNECTION.invalid:
                 driver, CONNECTION = SPECIFIER.connect()
         connection = CONNECTION
         if SCHEMA is None:
                 import schema
                 schema.schema.resolve( driver )
                 SCHEMA = schema.schema
                 PERSONS = SCHEMA.lookupName( 'persons' )
                 if hasattr( driver, 'listTables' ):
                         tables = driver.listTables( connection )
                         if 'persons' not in tables:
                                 # need to build the database...
                                 from pytable import installschema
                                 installschema.installSchema(
                                         connection,
                                         SCHEMA,
                                 )
                                 connection.commit()
         return connection

def duplic(fname,lname,msg,ok):
         dup = False
         for record in getperson( fname,lname ):
                 return "Duplicate Entry: %s,%s %s" %(fname,lname, msg), False
         return msg, ok

def duplicedit(fname,lname,id,msg,ok):
         dup = False
         for record in getperson( fname,lname, notID = id ):
                 return "Duplicate Entry: %s,%s %s" %(fname,lname, msg), False
         return msg, ok

def getperson( fname=None, lname=None, id=None, notID=None ):
         """Get person given sub-set of parameters"""
         wheres = []
         for field,value in [
                 ('fname',fname),
                 ('lname',lname),
                 ('id',id),
         ]:
                 if value is not None:
                         wheres.append( '%(field)s=%%(%(field)s)s'%locals())
         if notID is not None:
                 wheres.append( 'id != %(notID)s' )
         if wheres:
                 wheres = ' WHERE ' + (" AND ".join( wheres ))
         else:
                 wheres = ""
         connection = dbi()
         return PERSONS.query(
                 """SELECT * FROM persons %(wheres)s ORDER BY lname,fname;""",
                 connection,
                 wheres = wheres,
                 fname = fname,
                 lname = lname,
                 id = id,
                 notID = notID,
         )

def getpersonparms(id):
         for record in getperson( id=id ):
                 return record
         raise KeyError( "Unknown person id %r"%(id,))

def getpersons():
         return getperson()

def getname(id):
         try:
                 for person in getperson(fname,lname):
                         return person.fname
         except:
                 return ''

def getid(fname,lname):
         try:
                 for person in getperson(fname,lname):
                         return person.id
         except Exception, err:
                 traceback.print_exc()
                 return ''

def deleteperson(id):
         # really should use deleteQuery on the record object...
         connection = dbi()
         delete = 0
         try:
                 PERSONS.itemClass( id = id ).deleteQuery( connection )
                 connection.commit()
         except:
                 delete = 1
                 connection.rollback()
         return delete

def updateperson(parms,id):
         connection = dbi()
         try:
                 # normally you would use a record directly to
                 # do this updating, which requires us to do an
                 # extra query to get the old primary key to do
                 # the updates...
                 person = PERSONS.itemClass( id=id )
                 person.refreshQuery( connection )
                 for key,value in parms.items():
                         setattr(person, key,value)
                 person.updateQuery( connection )
                 connection.commit()
         except Exception, err:
                 traceback.print_exc()
                 raise

def enterperson(parms):
         connection = dbi()
         try:
                 newID = 0
                 for record in sqlquery.SQLQuery(
                         """SELECT max(id) FROM persons;""",
                 )( connection ):
                         try:
                                 newID = record[0]+1
                         except TypeError, err:
                                 # attempted to add to None
                                 newID = 1
                         break
                 person = PERSONS.itemClass( id=newID, **parms )
                 person.insertQuery( connection )
                 connection.commit()
         except Exception, err:
                 traceback.print_exc()
                 raise

SQL for PyTable

from pytable.schemabuilder import *

schema = database(
         name="addressdemo",
         comment = """Database for the "ready-to-run" wxPython demo""",
         tables = [
                 table(
                         "persons",
                         comment ="""Storage for the simple person-editing demo""",
                         fields = [
                                 field(
                                         # PostgreSQL's serial type is not supported by many database engines
                                         "id", "integer", 0, """Serial identity field for the person""",
                                         constraints=[ unique()],
                                 ),
                                 field(
                                         "fname", "varchar", 25, """Given (first) name for the individual""",
                                         defaultValue = "''",
                                         constraints=[ notNull()],
                                 ),
                                 field(
                                         "lname", "varchar", 25, """Family (last) name for the individual""",
                                         defaultValue = "''",
                                         constraints=[ notNull()],
                                 ),
                                 field(
                                         "salutation", "varchar", 10, """Appropriate greeting/salutation for written correspondence""",
                                         defaultValue = "''",
                                         constraints=[ notNull()],
                                 ),
                                 field(
                                         "profession", "varchar", 25, """Profession in which the individual is involved""",
                                         defaultValue = "''",
                                         constraints=[ notNull()],
                                 ),
                         ],
                         indices = [
                                 index( name='personkey', primary=True, fields=('fname','lname') ),
                         ],
                 ),
         ],
)

FullyFunctionalDemo (last edited 2010-05-27 06:52:03 by wl-ol-s246-45)

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