Although it works, there are various things that could be improved. This is often the case with big programs - the developers never truly finish, they just get to a point where it's good enough to release to the public (or maybe they get to a point where they just can't be bothered any more).
So what sort of improvements would I make?
- For the viewtable() function I could add an option to sort by different columns. At the moment it just sorts the names alphabetically, but I can imagine the use of sorting by year of birth or nation.
- For the SQLite database I could add a field with a web address for the person's Wikipedia entry. That then brings in the question of the best way for users of the Python program to find and use this web address.
- For searchtable() function I need to update the way the results are presented - it currently uses an old version of the code to present the table onscreen, which has been updated for viewtable(). This raises another possibility - creating another function to present tables onscreen that is shared by both viewtable() and searchtable(). After all, one of the big advantages of user-defined functions is that they can be called on by different parts of the program, thus eliminating the need to copy and paste commonly used code from one part of the program to another (which is what happened here).
- The searchtable() function no longer needs the diagnostic printing of the LIKE terms. Print statements can be useful for debugging (and they were useful when getting this program to work) but when no longer needed they should at least be hashed out as a comment or preferably deleted altogether.
- For the SQLite database I could replace the Role column with a new table, which would allow one person to have as many roles as seems appropriate. This aspect of databases involving multiple tables is worthy of a post on its own - see the next one.
- One aspect that can be improved for all functions is validating inputs, both for selecting options in the program and data input to be stored on the database - the most important one being checking that integers are integers. If SQLite receives a string/text input when it is expecting a number, it will throw an error, causing the Python program to crash.
import sqlite3
conn = sqlite3.connect("C:\\sqlite\\db\\history1.db")
curs = conn.cursor()
def displaytable(dump):
print("Name | Born | Died | Role | Nation")
print("======================|=======|=======|=======================|=========")
for line in dump:
namestr = line[0]
if len(namestr) >= 23:
namestr = namestr[0:22]
rolestr = line[3]
if len(rolestr) >= 23:
rolestr = rolestr[0:22]
nationstr = line[4]
if len(nationstr) >= 17:
nationstr = nationstr[0:16]
gapname = " " * (22 - len(namestr)) + "| "
gapborn = " | "
gapdied = " | "
gaprole = " " * (22 - len(rolestr)) + "| "
print(namestr + gapname + str(line[1]) + gapborn + str(line[2]) + gapdied + rolestr + gaprole + nationstr)
def viewtable():
print("Viewing Whole Table")
print("Sort table by: N for name, B for year of birth, D for year of death, R for role, T for nation")
sortby = input("? ")
sortby = sortby.lower()
sortdic = {'n':'Name',
'b':'YearBirth',
'd':'YearDeath',
'r':'Role',
't':'Nation'}
if sortby in sortdic.keys():
sortfield = sortdic[sortby]
else:
print('Sorry, option not recognised')
return
curs.execute("SELECT * FROM historicalfigures ORDER BY "+ sortfield +";")
fulldump = curs.fetchall()
displaytable(fulldump)
def searchtable():
print("Searching table - please select which column ")
print("Name ----(N)")
print("Born ----(B)")
print("Died ----(D)")
print("Role ----(R)")
print("Nation --(T)")
fieldchoice = input("? ")
fieldchoice = fieldchoice.lower()
if fieldchoice == "n":
term = input("What name? ")
searchterm = "Name = '" + term + "'"
elif fieldchoice == "b":
compare = input("Do you want results before ( < ), in exact year ( = ) or after ( > )? ")
term = input("Which Year? ")
if checkint(term):
searchterm = "YearBirth "+ compare + " " + term
else:
return
elif fieldchoice == "d":
field = "YearDeath"
compare = input("Do you want results before ( < ), in exact year ( = ) or after ( > )? ")
term = input("Which Year? ")
if checkint(term):
searchterm = "YearDeath "+ compare + " " + term
else:
return
elif fieldchoice == "r":
term = input("What Role? ")
searchterm = "Role LIKE '%"+ term +"%'"
elif fieldchoice == "t":
term = input("What Nation? ")
searchterm = "Nation LIKE '%" + term + "%'"
else:
print("Sorry, option not recognised. ")
return()
# print (searchterm)
curs.execute("SELECT * FROM historicalfigures WHERE " + searchterm +";")
searchdump = curs.fetchall()
displaytable(searchdump)
def enternew():
print("Entering new record")
newname = input("Please enter new name: ")
newbirth = input("Please enter new year of birth: ")
if not checkint(newbirth):
print("Not an integer.")
return
newdeath = input("Please enter new year of death: ")
if not checkint(newdeath):
print("Not an integer.")
return
newrole = input("Please enter new role: ")
newnation = input("Please enter new nation: ")
keeper = input("Do you want to keep this new record (Y to keep, N to discard): ")
keeper = keeper.lower()
if keeper == "y":
curs.execute("INSERT INTO historicalfigures (Name, YearBirth, YearDeath, Role, Nation) VALUES ('" + newname + "', "+ newbirth +", "+ newdeath +", '" + newrole + "', '" +newnation +"');")
curs.execute("COMMIT;")
def deleterecord():
curs.execute("SELECT Name FROM historicalfigures ORDER BY Name;")
namedump = curs.fetchall()
print("Current list of names is:")
linecount = 0
for line in namedump:
print(linecount + " - " + line[0])
linecount += 1
whichline = input("Enter number of row to be deleted: ")
whichline = int(whichline)
delname = namedump[whichline][0]
confirm = input("Please confirm (Y) or cancel (N) deleting record for " + delname +": ")
confirm = confirm.lower()
if confirm == 'y':
curs.execute("DELETE FROM historicalfigures WHERE Name ='" + delname + "';")
curs.execute("COMMIT;")
def updaterecord():
curs.execute("SELECT Name FROM historicalfigures ORDER BY Name;")
namedump = curs.fetchall()
print("Current list of names is:")
linecount = 0
for line in namedump:
print(str(linecount) + " - " + line[0])
linecount += 1
whichline = input("Enter number of row to be changed: ")
whichline = int(whichline)
updatename = namedump[whichline][0]
curs.execute("SELECT * FROM historicalfigures WHERE Name = '"+ updatename+"';")
changedump = curs.fetchall()
coltuple = ('Name', 'YearBirth', 'YearDeath', 'Role', 'Nation')
colcount = 0
for col in changedump[0]:
print (str(colcount), coltuple[colcount], changedump[0][colcount])
colcount += 1
colchoice = input("Please enter number of column to change: ")
colchoice = int(colchoice)
newvalue = input("Please enter new value for this column: ")
if colchoice == 0 or colchoice == 3 or colchoice == 4:
newvalue = "'"+ newvalue +"'"
elif colchoice == 1 or colchoice == 2:
if not checkint(newvalue):
return
print("About to change "+ coltuple[colchoice] +" to " + newvalue + " for "+ updatename)
confirmchange = input("Confirm(Y) or discard (N): ")
confirmchange = confirmchange.lower()
if confirmchange == 'y':
curs.execute("UPDATE historicalfigures SET "+ coltuple[colchoice] +" = "+ newvalue +" WHERE Name = '" + updatename + "';")
curs.execute("COMMIT;")
def checkint(num):
try:
int(num)
check = True
except:
print("Not a valid number!")
check = False
return check
#Main program
print ("Historical Figures Table")
print ("========================")
cont = True
while cont == True:
print ("Main menu - Please select an option")
print ("View whole table ---V")
print ("Search table -------S")
print ("Enter new record ---E")
print ("Delete record ------D")
print ("Update record ------U")
print ("Quit Program -------Q")
optchoice = input("? ")
optchoice = optchoice.lower()
if optchoice == 'v':
viewtable()
elif optchoice == 's':
searchtable()
elif optchoice == 'e':
enternew()
elif optchoice == 'd':
deleterecord()
elif optchoice == 'u':
updaterecord()
elif optchoice == 'q':
conn.close()
cont = False
else:
print("Sorry, not recognised")
So this version addresses points 1, 3, 4 and partially 6. Points 2 and 5 require work on the SQLite database, to be done later.
To validate that integers are entered when required, I've added a new function - checkint() which uses try...except... structure. I admit it's not perfect - rather than asking the user to reenter the number, it simply returns to the main menu. This could be improved upon.
The function viewtable() now has an additional step where the user selects what column to sort by. Rather than going through a set of if...elif...else... statements (which I have used in other parts of the program), I've used a dictionary to link the user's choice to the right column.
Finally both viewtable() and searchtable() call the function displaytable() to print it on screen. Unlike most functions in this program, displaytable() takes the output from curs.fetchall() (i.e. a list of tuples) as an argument.
No comments:
Post a Comment