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') ),
],
),
],
)