The full code for version 4 of the program is as follows:
import sqlite3
conn = sqlite3.connect("C:\\sqlite\\db\\history1.db")
curs = conn.cursor()
#List of User-defined Functions
#addlink()
#addrole()
#checkint()
#deleterecord()
#deleterole()
#deletelink()
#displayrecord()
#displaytable()
#enternew()
#searchtable()
#updaterecord()
#viewtable()
def addlink():
print("Adding Web link")
curs.execute("SELECT Name FROM historicalfigures ORDER BY Name;")
namedump = curs.fetchall()
namelist = []
for line in namedump:
namelist.append(line[0])
print (namelist.index(line[0]), line[0])
namech = input("Enter number of name to add web link to: ")
if checkint(namech) and int(namech) < len(namelist):
newname = namelist[int(namech)]
print("Record selected is " + newname)
linkaddress = input("Please enter web address: ")
linknote = input("Note to describe link? ")
keeper = input("Do you wish to keep this new link (Y/N)? ")
keeper = keeper.lower()
if keeper == 'y':
curs.execute("INSERT INTO weblinks (Name, weblink, notes) VALUES ('"+newname+"', '"+linkaddress+"', '"+linknote+"');")
curs.execute("COMMIT;")
def addrole():
curs.execute("SELECT Name from historicalfigures ORDER BY Name;")
namedump = curs.fetchall()
namelist = []
for line in namedump:
namelist.append(line[0])
print (namelist.index(line[0]), line[0])
namech = input("Enter number of name to add role to: ")
if checkint(namech) and int(namech) < len(namelist):
newname = namelist[int(namech)]
Role = input("Please enter new role: ")
keeper = input("Do you wish to keep this new role (Y/N)? ")
keeper = keeper.lower()
if keeper == 'y':
curs.execute("INSERT INTO roles (Name, Role) VALUES ('"+newname+"', '"+ Role +"');")
curs.execute("COMMIT;")
def checkint(num):
try:
int(num)
check = True
except:
print("Not a valid number!")
check = False
return check
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(str(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("DELETE FROM roles WHERE Name = '"+ delname + "';")
curs.execute("DELETE FROM weblinks WHERE Name = '"+ delname + "';")
curs.execute("COMMIT;")
def deletelink():
curs.execute("SELECT Name, notes, weblink FROM weblinks ORDER BY Name;")
linkdump = curs.fetchall()
rowcount = 0
for line in linkdump:
print(rowcount, line[0], line[1], line[2])
rowcount += 1
linechoice = input("Which row should be deleted?")
if checkint(linechoice) and int(linechoice) < rowcount:
rowchoice = int(linechoice)
print("You have selected: ")
print(linkdump[rowchoice][0], linkdump[rowchoice][1], linkdump[rowchoice][2])
confirm = input("Confirm delete (Y/N)?")
confirm = confirm.lower()
if confirm == 'y':
curs.execute("DELETE FROM weblinks WHERE Name = '"+ linkdump[rowchoice][0] +"' AND weblink = '"+ linkdump[rowchoice][0] +"';")
def deleterole():
curs.execute("SELECT Name, Role FROM roles ORDER BY Name;")
roledump = curs.fetchall()
nameset = set()
for line in roledump:
nameset.add(line[0])
namelist = list(nameset)
namelist.sort()
print("Deleting from Roles Table")
for name in namelist:
print (namelist.index(name), name)
numchoice = input("Please enter which name to delete role from: ")
namechoice = namelist[int(numchoice)]
rolelist = []
for line in roledump:
if line[0] == namechoice:
rolelist.append(line[1])
for r in rolelist:
print (rolelist.index(r), r)
delrolenum = input("Which role number do you wish to delete? ")
if checkint(delrolenum) and int(delrolenum) < len(rolelist):
delrole = rolelist[int(delrolenum)]
confirm = input("Confirm delete " + namechoice + " role as " + delrole + "(Y/N)? ")
confirm = confirm.lower()
if confirm == 'y':
curs.execute("DELETE FROM roles WHERE Name = '"+ namechoice +"' AND Role = '"+ delrole +"';")
curs.execute("COMMIT;")
curs.execute("SELECT Role FROM roles WHERE Name = '"+ namechoice +"';")
currentrolesdump = curs.fetchall()
currentroles = ''
for line in currentrolesdump:
for col in line:
currentroles += col + ', '
print("Current roles are now: " + currentroles)
def displayrecord(name):
curs.execute("SELECT * FROM historicalfigures WHERE Name = '"+ name +"';")
maindump = curs.fetchall()
curs.execute("SELECT * FROM roles WHERE Name = '"+ name +"';")
rolesdump = curs.fetchall()
curs.execute("SELECT * FROM weblinks WHERE Name = '"+ name +"';")
webdump = curs.fetchall()
mainrecord = maindump[0]
print("Name: " + name)
print("Born: " + str(mainrecord[1]))
print("Died: " + str(mainrecord[2]))
print("Nation: " + mainrecord[4])
rolestring = ""
for line in rolesdump:
rolestring = rolestring + line[1] +", "
print("Roles: " + rolestring)
if webdump: # does webdump contain any lines?
print("Web addresses for more info")
for line in webdump:
print(line[1])
def displaytable(dump):
print("No. | Name | Born | Died | Nation")
print("=====|=======================|=======|=======|=====================")
counter = 1
for line in dump:
counterstr = " "* (4- len(str(counter)))+ str(counter) +" | "
namestr = line[0]
if len(namestr) >= 23:
namestr = namestr[0:22]
nationstr = line[4]
if len(nationstr) >= 20:
nationstr = nationstr[0:19]
gapname = " " * (22 - len(namestr)) + "| "
gapborn = " | "
gapdied = " | "
print(counterstr + namestr + gapname + str(line[1]) + gapborn + str(line[2]) + gapdied + nationstr)
counter += 1
print("Enter the record number if you want further details or just enter to return to main menu.")
displaychoice = input("? ")
if checkint(displaychoice):
choi = int(displaychoice) -1
if choi >= 0 and choi < (len(dump) +1):
displayrecord(dump[choi][0])
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
newnation = input("Please enter new nation: ")
newrole = input("Please enter new roles, separated by commas: ")
templist = newrole.split(',')
rolelist = []
for r in templist:
if r[0] == ' ':
r = r[1:]
r = r.rstrip()
rolelist.append(r)
print(rolelist)
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, Nation) VALUES ('" + newname + "', "+ newbirth +", "+ newdeath +", '" +newnation +"');")
for newrole in rolelist:
print("INSERT INTO roles (Name, Role) VALUES ('" + newname + "', '" + newrole +"');")
curs.execute("INSERT INTO roles (Name, Role) VALUES ('" + newname + "', '" + newrole +"');")
curs.execute("COMMIT;")
def searchtable():
print("Searching table - please select which column ")
print("Name ----(N)")
print("Born ----(B)")
print("Died ----(D)")
print("Nation --(T)")
print("Role ----(R)")
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? ")
term = term.capitalize()
searchterm = " Name in (SELECT Name FROM roles WHERE Role = '"+ term +"')" # To be completed
elif fieldchoice == "t":
term = input("What Nation? ")
searchterm = "Nation LIKE '%" + term + "%'"
else:
print("Sorry, option not recognised. ")
return()
executable = "SELECT * FROM historicalfigures WHERE " + searchterm +";"
print (executable)
curs.execute(executable)
searchdump = curs.fetchall()
if searchdump:
displaytable(searchdump)
else:
print("Sorry, no records found that match the criteria")
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: ")
if checkint(whichline):
whichline = int(whichline)
else:
print("Sorry, not a valid selection")
return
updatename = namedump[whichline][0]
curs.execute("SELECT * FROM historicalfigures WHERE Name = '"+ updatename+"';")
changedump = curs.fetchall()
curs.execute("SELECT Role FROM roles WHERE Name ='" + updatename + "';")
roledump = 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
for row in roledump:
print ("role " + str(colcount), row[0])
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 +"'"
table = "historicalfigures"
field = coltuple[colchoice]
if colchoice == 0:
print("Updating Name will affect records in other tables that use this name")
elif colchoice == 1 or colchoice == 2:
field = coltuple[colchoice]
table = "historicalfigures"
if not checkint(newvalue):
return
elif colchoice > 4 and colchoice < colcount:
table = "roles"
field = "role"
newvalue = "'"+ newvalue +"'"
oldvalue = roledump[colchoice -5][0]
print("About to change "+ field +" to " + newvalue + " for "+ updatename)
confirmchange = input("Confirm(Y) or discard (N): ")
confirmchange = confirmchange.lower()
if confirmchange == 'y':
if table == "historicalfigures":
executable = "UPDATE "+ table +" SET "+ field +" = "+ newvalue +" WHERE Name = '" + updatename + "';"
elif table == "roles":
executable = "UPDATE "+ table +" SET "+ field +" = "+ newvalue +" WHERE Name = '" + updatename + "' AND role = '" + oldvalue + "';"
#print(executable)
curs.execute(executable)
if colchoice == 0:
executable_role = "UPDATE roles SET Name = "+ newvalue +" WHERE Name = '" + updatename + "';"
print(executable_role)
curs.execute(executable_role)
executable_link = "UPDATE weblinks SET Name = "+ newvalue +" WHERE Name = '"+ updatename + "';"
print(executable_link)
curs.execute(executable_link)
curs.execute("COMMIT;")
def viewtable():
print("Viewing Main Table")
print("Sort table by: N for name, B for year of birth, D for year of death, T for nation")
sortby = input("? ")
sortby = sortby.lower()
sortdic = {'n':'Name',
'b':'YearBirth',
'd':'YearDeath',
'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)
#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")
print ("Add/Delete Role ----R")
print ("Add/Delete Weblink -W")
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
elif optchoice == 'r':
rolech = input ("Add (A) or Delete (D)? ")
rolech = rolech.lower()
if rolech == 'd':
deleterole()
elif rolech == 'a':
addrole()
elif optchoice == 'w':
linkch = input("Add (A) or Delete (D)? ")
linkch = linkch.lower()
if linkch == 'd':
deletelink()
elif linkch == 'a':
addlink()
else:
print("Sorry, not recognised")
That is a lot - Sorry about that for those who don't like wading through pages of code. But you can see why my rate of posting has slowed.
So does it work? Generally speaking yes. It does what I intended it to. If the inputs from the user are valid it does not throw any errors. I have not quite ironed out all the errors caused by bad inputs. There are also some improvements that could be made, but I've decided to leave those to version 5 of the program.
No comments:
Post a Comment