Modify the solution from Exercise 17.7 so that the program checks whether a record already exists in the database before adding it.

Question

What will be an ideal response?

Answer 1

```
# Inserts into, updates and searches a database.

import MySQLdb
from Tkinter import *
from tkMessageBox import *
import Pmw

class AddressBook( Frame ):
"""GUI Database Address Book Frame"""

def __init__( self ):
"""Address Book constructor"""

Frame.__init__( self )
self.pack( expand = YES, fill = BOTH )
self.master.title( "Address Book Database Application" )

# buttons to execute commands
self.buttons = Pmw.ButtonBox( self, padx = 0 )
self.buttons.grid( columnspan = 2 )
self.buttons.add( "Find", command = self.findAddress )
self.buttons.add( "Add", command = self.addAddress )
self.buttons.add( "Update",
command = self.updateAddress )
self.buttons.add( "Clear", command = self.clearContents )
self.buttons.add( "Help", command = self.help,
width = 14 )
self.buttons.alignbuttons()


# list of fields in an address record
fields = [ "ID", "First name", "Last name", "Address",
"City", "State Province", "Postal Code", "Country",
"Email Address", "Home phone", "Fax Number" ]

# dictionary with Entry widgets for values, keyed by
# corresponding addresses table field names
self.entries = {}

self.IDEntry = StringVar() # current address id text
self.IDEntry.set( "" )

# create entries for each field
for i in range( len( fields ) ):
label = Label( self, text = fields[ i ] + ":" )
label.grid( row = i + 1, column = 0 )
entry = Entry( self, name = fields[ i ].lower(),
font = "Courier 12" )
entry.grid( row = i + 1 , column = 1,
sticky = W+E+N+S, padx = 5 )

# user cannot type in ID field
if fields[ i ] == "ID":
entry.config( state = DISABLED,
textvariable = self.IDEntry, bg = "gray" )

# add entry field to dictionary
key = fields[ i ].replace( " ", "_" )
key = key.upper()
self.entries[ key ] = entry

# buttons to navigate multiple entries
self.buttons = Pmw.ButtonBox( self, padx = 10 )
self.buttons.grid( columnspan = 2 )
self.buttons.add( "Back", command = self.moveBack )
self.buttons.add( "Forward", command = self.moveForward )
self.buttons.alignbuttons()

self.setButtonState( DISABLED )

def setButtonState( self, newState ):
"""Set the state of back/forward buttons"""

self.buttons.button( 0 ).config( state = newState )
self.buttons.button( 1 ).config( state = newState )

def moveBack( self ):
"""Move one entry back"""

self.current -= 1
self.displayResults()

def moveForward( self ):
"""Move one entry forward"""

self.current += 1
self.displayResults()

def addAddress( self ):
"""Add address record to database"""

if self.entries[ "LAST_NAME" ].get() != "" and \
self.entries[ "FIRST_NAME"].get() != "":

# create SELECT query command
selectQuery = "SELECT * FROM addresses WHERE " + \
"FIRST_NAME = '%s' AND LAST_NAME " % \
self.entries[ "FIRST_NAME" ].get() + \
"= '%s' AND HOME_PHONE = '%s'" % \
( self.entries[ "LAST_NAME" ].get(),
self.entries[ "HOME_PHONE" ].get() )

# create INSERT query command
query = "INSERT INTO addresses (" + \
"FIRST_NAME, LAST_NAME, ADDRESS, CITY, " + \
"STATE_PROVINCE, POSTAL_CODE, COUNTRY, " + \
"EMAIL_ADDRESS, HOME_PHONE, FAX_NUMBER" + \
") VALUES (" + \
"’%s’, " * 10 % \
( self.entries[ "FIRST_NAME" ].get(),
self.entries[ "LAST_NAME" ].get(),
self.entries[ "ADDRESS" ].get(),
self.entries[ "CITY" ].get(),
self.entries[ "STATE_PROVINCE" ].get(),
self.entries[ "POSTAL_CODE" ].get(),
self.entries[ "COUNTRY" ].get(),
self.entries[ "EMAIL_ADDRESS" ].get(),
self.entries[ "HOME_PHONE" ].get(),
self.entries[ "FAX_NUMBER" ].get() )
query = query[ :-2 ] + ")"

# open connection, retrieve cursor and execute query
try:
connection = MySQLdb.connect( db = "AddressBook" )
cursor = connection.cursor()
cursor.execute( selectQuery )
results = cursor.fetchall()

# record does not exist
if results == None:
cursor.execute( query )
else: # record exists
showerror( "Error", "Record already exists." )

except MySQLdb.OperationalError, message:
errorMessage = "Error %d:\n%s" % \
( message[ 0 ], message[ 1 ] )
showerror( "Error", errorMessage )
self.clearContents()
else:
self.clearContents()
cursor.close()
connection.close()

else: # user has not filled out first/last name fields
showwarning( "Missing fields", "Please enter name" )

def findAddress( self ):
"""Query database for address record and display results"""

if self.entries[ "LAST_NAME" ].get() != "":

# create SELECT query
query = "SELECT * FROM addresses " + \
"WHERE LAST_NAME = ’" + \
self.entries[ "LAST_NAME" ].get() + "’"

# open connection, retrieve cursor and execute query
try:
connection = MySQLdb.connect( db = "AddressBook" )
cursor = connection.cursor()
cursor.execute( query )
except MySQLdb.OperationalError, message:
errorMessage = "Error %d:\n%s" % \
( message[ 0 ], message[ 1 ] )
showerror( "Error", errorMessage )
self.clearContents()
else: # process results
self.results = cursor.fetchall()
self.fields = cursor.description

# no results for this person
if not self.results:
showinfo( "Not found", "Nonexistent record" )
else: # display person’s info. in GUI
self.clearContents()

# display results for the first person
self.current = 0
self.displayResults()

cursor.close()
connection.close()

else: # user did not enter last name
showwarning( "Missing fields",
"Please enter last name" )

def displayResults( self ):
"""Display person information for current index"""

self.clearContents()

if len( AddressBook.results ) == 0:
return

which = self.current
self.setButtonState( NORMAL )

# check index boundaries
if which <= 0:
which = 0
self.buttons.button( 0 ).config( state = DISABLED )

if which >= len( self.results ) - 1:
which = len( self.results ) - 1
self.buttons.button( 1 ).config( state = DISABLED )

for i in range( len( self.fields ) ):

if self.fields[ i ][ 0 ] == "ID":
self.IDEntry.set( \
str( self.results[ which ][ i ] ) )
else:
self.entries[ self.fields[ i ][ 0 ] ]. \
insert( INSERT, str \
(self.results[ which ][ i ] ) )

self.current = which

def updateAddress( self ):
"""Update address record in database"""

if self.entries[ "ID" ].get() != "":

# create UPDATE query command
entryItems= self.entries.items()
query = "UPDATE addresses SET"

for key, value in entryItems:
if key != "ID":
query += " %s=’%s’," % ( key, value.get() )

query = query[ :-1 ] + " WHERE ID=" + \
self.IDEntry.get()

# open connection, retrieve cursor and execute query
try:
connection = MySQLdb.connect( db = "AddressBook" )
cursor = connection.cursor()
cursor.execute( query )
except MySQLdb.OperationalError, message:
errorMessage = "Error %d:\n%s" % \
( message[ 0 ], message[ 1 ] )
showerror( "Error", errorMessage )
self.clearContents()
else:
showinfo( "database updated", "Database Updated." )
cursor.close()
connection.close()

else: # user has not specified ID
showwarning( "No ID specified", \
"You may only update" + \
" an existing record.\nUse Find to" + \
" locate the record,\nthen modify the" + \
" information and press Update." )

def clearContents( self ):
"""Clear GUI panel"""

self.setButtonState( DISABLED )

for entry in self.entries.values():
entry.delete( 0, END )

self.IDEntry.set( "" )

def help( self ):
"""Display help message to user"""

showinfo( "Help", "Click Find to locate a record.\n" + \
"Use Forward/Back to browse through entries.\n" + \
"Click Add to insert a new" + \
" record.\nClick Update to update" + \
" the information in a record.\n" + \
"Click Clear to empty" + \
" the Entry fields.\n" )

def main():
AddressBook().mainloop()

if __name__ == "__main__":
main()
```