Progressing through Python: October 2018

Wednesday, 31 October 2018

Creating web pages using Python

Much of today's computer usage that I am aware of occurs on the World Wide Web. Social media, news websites, forums, wiki pages, homepages for movie streaming, internet shopping and auctioning, the list goes on.
Can you create web pages with Python? Yes.
Let me qualify that. Python can help automate and speed up writing the HTML. However, on its own it is not a web hosting service, so using it to create web pages that the rest of the world can visit will require more than just Python.

In terms of viewing web pages and opening up web browsers, the module I have found useful is webbrowser, which can be imported like other modules. For example, creating a very simple web page in Python then opening it in a browser:

import webbrowser
fhandle = open('temp.html', 'w')
fhandle.write("<!DOCTYPE html> \n")
fhandle.write("<html> \n")
fhandle.write("<body> \n")
fhandle.write("<H1>Hello World Wide Web!</H1> \n")
fhandle.write("</body> \n")
fhandle.write("</html> \n")
fhandle.close()
webbrowser.open_new_tab('temp.html')

HTML (Hyper Text Mark-up Language) is of course the basic language of the web, and many pages are created using it (especially the older ones). Like Python it is a computer language, but while Python is a programming language and SQLite is a query language, HTML is a mark-up language, which basically tells the web browser on your computer how to display the content downloaded as the web page. I won't go into the details of writing HTML, but there are plenty of books and web pages to help you. Here we are creating the HTML as strings and writing it into the new file (here temp.html).

You can also use the webbrowser module to open pages hosted elsewhere if you know the web address. For example:

import webbrowser
webpage = "https://www.bbc.co.uk/news"
webbrowser.open(webpage)
webpagelist = ["https://apod.nasa.gov/apod/astropix.html",
               "https://en.wikipedia.org/wiki/Main_Page",
               "https://www.youtube.com/?hl=en-GB&gl=GB"]
for page in webpagelist:
    webbrowser.open(page)

The webbrowser module has several functions, including
webbrowser.open('web address')
This is the basic function that opens the web page in the default browser.

webbrowser.open_new('web address')
This function opens the page in a new window in the default browser.

webbrowser.open_new_tab('web address')
And this one opens the page in a new tab.
You don't really need Python to open up a web page, and it's a programming language, not a search engine. So why bother?
The real benefit of Python is in creating new web pages where the content is contingent on inputs, i.e. web pages that have variable content. I have already used Python to create HTML tables. This module allows you to create and display the table in the same program. So if we have a text file with the following content:
Aardonyx, Africa, 195, Sauropod-prosauropod, https://en.wikipedia.org/wiki/Aardonyx
Abelisaurus, S-America, 80, Therapod-abelisaur, https://en.wikipedia.org/wiki/Abelisaurus
Abrictosaurus, Africa, 200, Ornithopod, https://en.wikipedia.org/wiki/Abrictosaurus

Abydosaurus, N-America, 105, Sauropod, https://en.wikipedia.org/wiki/Abydosaurus

..
..
..

we can create an HTML table using the contents and even turn the web addresses for each line into hyperlinks. For those who are wondering, this text file is from a previous project from before I started this blog, with 150 dinosaur genera.
Here is the program that does that.
import webbrowser
fhandle = open('dinodata2.txt', 'r')
totallist = []
for line in fhandle:
    linelist = line.split(',')
    linelist[4].rstrip()
    totallist.append(linelist)
fhandle.close()
xhandle = open('tempweb.html', 'w')
xhandle.write("""
<!DOCTYPE html>
<html>
<head>
<title>Dinosaur List</title>
</head>
<body>
<h1>Dinosaur List</h1>
<table style="border: 1px solid black;">
<tr><td>Genus Name</td><td>Continent</td><td>Millions of Years Ago</td><td>Group</td><td>Wiki Link</td></tr>
""")
for line in totallist:
    xhandle.write("<tr>")
    for cell in line:
        if cell == line[4]:
            xhandle.write("<td style='border: 1px solid black;'><a href='"+cell+"'>Wikipedia Link</a></td>")
        else:
            xhandle.write("<td style='border: 1px solid black;'>" +cell+"</td>")
    xhandle.write("</tr>")
xhandle.write("""
</table>
</body>
</html>""")
xhandle.close()
webbrowser.open('tempweb.html')

The results are basic - someone who knows either HTML or CSS (Cascading Style Sheet) formatting could add more style, but the important thing is it reads from a text file and creates a web page with a table, filled with data from the text file, and hyperlinks for web addresses (here the relevant Wikipedia page). It actually stores the content from the text file as a list of lists (here totallist), then uses that list of lists to create the html table. I guess the next step would to be add sorting and filters to this table so you can look for dinosaurs according to parameters.
The final line webbrowser.open('tempweb.html') shows that the webpage can be local rather than on a remote server - the web address can be replaced by a file path on the local drive or network. Here the file tempweb.html is created in the same folder as the Python program.

Wednesday, 24 October 2018

A Very Simple Adventure Program

This program was prompted by my browsing a list of challenges for students of computer programming, one of which was "Create a text-based adventure game". My thoughts went to those Fighting Fantasy books by Steve Jackson and Ian Livingstone, such as "Warlock of Firetop Mountain".

To be honest I've started off simple. The only things that the program keeps track of are what section the user is currently on and what their current stamina (life points) score is. The idea of keeping track of non-player creatures (that might move around from one location to another or die) and movable items (that might be picked up, dropped or consumed) I put aside until I had got a grip on the basics.
I decided to do reading the data file (just a text file) and setting up as the main program and the repeated process of going from one section of text to the next as a user-defined function. Here is the whole program:

import sys

def nextparagraph(para, stam):
print("*************")
print(paradic[para])
stam = deathcheck(para, stam)
if stam >= 1:
temptable = []
for entry in optionstable:
if entry[2] == para:
temptable.append(entry)
validchoice = False
while validchoice == False:
for entry in temptable:
optnum = temptable.index(entry)
print(optnum, entry[0])
option = int(input("Option: "))
if option >= 0 and option < (len(temptable)):
validchoice = True
newpara = temptable[option][1]
nextparagraph(newpara, stam)

def deathcheck(para, stam):
for item in staminaChangeDic:
if item == para:
stam += staminaChangeDic[item]
if stam <= 0:
print("Sorry, you have died. Your stamina is", stam)
print("Better luck next time!")
sys.exit()
elif stam >=1000:
print("Congratulations, you have survived the cave!")
sys.exit()
else:
print("Current Stamina: ", stam)
return stam

FH = open('caverdata01.txt', 'r')
paralist = [] # list of entire, unparsed paragraphs
parastring = '' #empty string to hold contents of each paragraph
for line in FH:
#print (line)
if line == '---\n':
paralist.append(parastring)
parastring = ''
else:
parastring += line
FH.close()

optionstable = []
paradic = {}
staminaChangeDic = {}
for para in paralist:
templist = para.split('$')
pararef = templist[0].rstrip()
paraprose = templist[1]
if '//' in paraprose:
staminaAdjust = int(paraprose.split('//')[1])
paraprose = paraprose.split('//')[0]
staminaChangeDic[pararef] = staminaAdjust
for option in templist[2:]:
tempopt = option.split('Goto ')
tempopt[1] = tempopt[1].rstrip()
tempopt.append(pararef)
optionstable.append(tempopt)
paradic[pararef] = paraprose

stamina = 20
carryon = True
para = '0'
print("CAVE ADVENTURE!!!")
nextparagraph(para, stamina)

As you can see, it makes use of three dictionaries, holding the section reference as the key. The values are different for each dictionary - one holds the text description, one holds the options for each section and the third holds any stamina adjustments associated with a section.

The data file holds the section text , the modification to stamina score on choosing each section, and options for leaving each section. I decided to use symbols to indicate how the character should be split up - a sort of markup, though not as sophisticated as HTML. So in the data file three dashes --- indicate the breaks between sections, while the dollar sign $ is used to split up sections into section number/reference (actually a string, not an integer), the prose that is printed to describe the situation to the player, and the options with the section reference that each option leads to.
A double forward slash // is used at the end of the prose if there is a modification to stamina, and is followed by an integer for how much stamina is gained (or lost if the number is negative).
For each option, I separated the text to be displayed from the reference it leads to by the string "Goto". This is more nostalgia than anything else - it reminds me of my very early days of programming on a Commodore 64, where using GOTO statements was the norm rather than a blasphemy against proper programming. One advantage of doing the data this way is that it is fairly readable for whoever is creating it - it is almost but not quite like those adventure game books.
I'll only give the first three paragraphs, otherwise this post would get very long, and also I wouldn't want to spoil it for you.

0 $You find yourself in a dimly lit cavern. You are not
sure how you got here but you know you have to find a way
out. Checking yourself you find you have an electric
flashlight and a compass with which to navigate.
$You look around. Goto 1
---
1 $This is a relatively plain, featureless cavern with
rugged walls and fairly flat, gravel-like floor. Looking
around the cavern you see there are three tunnels exiting
this cavern.
$Do you go north? Goto 2
$Do you go east? Goto 3
$Do you go south? Goto 4
---
2 $This cavern has a pool of water in it. At a distance you
cannot see clearly into the pool. Also this cavern has two
exits, one heading south, one heading east.
$Do you investigate the pool? Goto 2a
$Do you go south? Goto 1
$Do you go east? Goto 5
---

I found I was starting to lose track of how each area was located relative to each other. So I did some documentation outside the code in the form of doing a map using presentation software. The result?

Tuesday, 23 October 2018

Failing Gracefully

Failing Gracefully is the idea that if something goes wrong when a program is running, the disruption this causes to the user is minimised. If possible the program keeps going, and even if it has to close, at least it can be polite and informative to the user as it does so. Ideally an end user should never have to see a raw syntax error on their screen. At the end of my previous post when discussing the historical figure database I said

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.

This is one important aspect of failing gracefully - coping with user error. Clearly the program isn't good if it fails and throws a syntax error when the user enters valid inputs. But a good program should cope with invalid inputs as well. In terms of the latest program I have created, most of the inputs are tested to see that they valid - there is a function in the program that is dedicated to making sure that inputs that are expected to be integers are indeed integers:

def checkint(num):
try:
int(num)
check = True
except:
print("Not a valid number!")
check = False
return check

Simple but it does its job. Note that it uses a try...except... structure, which is a very useful thing when checking for something that might cause syntax errors. If a syntax error would occur in a try...except... structure, it is caught before the program crashes, and the except part takes over.
Another aspect and set of errors is when the program needs to connect to an external resource, such as a file. This can happen when either opening a text file or when using the SQLite module to connect to a database file, or maybe trying to connect to a website. If the file doesn't exist, or maybe not in the folder the program is using as the path, then generally the program will throw an error and end. Are there alternatives? Could the programmer anticipate this? If the external file is essential for the running of the program then the best case scenario may be that the Python program closes, leaving an error message saying that it could not find the relevant file. Should the user be prompted to enter an alternative file path? Could the program run with a different file? Should this alternative file's name be included in the code or should the user be prompted to input it? As above, a try...except... structure can catch these problems before they cause the program to crash.
Asking the programmer to anticipate all of these problems could add substantially to the code, but not anticipating them at all is courting even more problems, especially when untrained users are let loose on the program. I admit that I do make assumptions about things working as expected (including user inputs) when creating the programs you have already seen on this blog. For example, the historical figures database assumes that SQLite is already installed, and that the history1.db with its three tables and their respective columns is already set up, ready to at least receive new data. That's simply how things are on my computer. Running the program on a different computer will cause problems because the program does not anticipate these differences between computers.

Friday, 19 October 2018

Documentation Outside of the Code

In professional development this is not just useful, it is obligatory. Where I am with my relatively small programs it is just starting to be useful. Commenting in the code is good and useful, but sometimes it isn't enough. This proved true with the Historical database, especially when going through the testing. A simple text file holding my thoughts and ideas about the database proved sufficient. A sample few lines from that text file:

View - Done; all options for sorting tested, and also tested viewing individuals, both with and without weblinks
Search tested - problem if no results found it still creates table and asks for which record to view. Now corrected with if searchdump: to check if searchdump is empty
New Record tested (Martin Luther)
When updating record, if no number is selected (for which record) it throws an error. Just hitting return should return to main menu. - Now corrected
When adding weblink, confirmation needed for which person is selected before adding weblink - Fixed!
Modify searching births and deaths to allow range of years to be searched - maybe leave to version 5
When trying to update record for a character's roles, I can select a role but when I enter the new role it throws an error.
Traceback (most recent call last):
File "C:\Users\pc\Documents\Programming\historical_4.py", line 362, in <module>
updaterecord()
File "C:\Users\pc\Documents\Programming\historical_4.py", line 303, in updaterecord
oldvalue = roledump[colcount-5][0]
IndexError: list index out of range
>>>
>>> conn = sqlite3.connect("C:\sqlite\db\history1.db")
>>> curs = conn.cursor()
>>> curs.execute("SELECT Role FROM roles WHERE Name ='Mickey Mouse';")
<sqlite3.Cursor object at 0x03C32EE0>
>>> roledump = curs.fetchall()
>>> print(str(roledump))
[('Cartoon Character',), ('Entertainer',)]
>>> Fixed now.
When Updating a record and user changes the name, the subtables are not updated - i.e. the subtables use the old name and are no longer associated with that record
Fixed by inserting the following code just before the COMMIT
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)
Deleting link - at the moment it relies on there being only a few entries in the table - it prints out all links for all names. This may need to change when more links are added, so that the user selects the historical name first then which link associated with that name to delete. Probably leave until version 5

I've adjusted the fonts to clarify what's what. When testing, I will encounter either problems or possible improvements. Jotting them down in this text file gives me a to-do list, which I can cross off as I address each problem.
From a professional perspective this is not very neat or well-structured. At the moment it doesn't have to be - it is purely for my own reference, there are no work colleagues or fellow developers who need to understand what's going on. Nonetheless, it is a start of what could be a very sensible habit.

Monday, 15 October 2018

The Historic Figures Database Program

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.

Saturday, 13 October 2018

Testing a program

Once a program reaches a certain size, and maybe has a few different functions and options, making sure it does what you intended becomes a task in itself. When you reach the level of professional software for public release, testing becomes an integral part of the process.
As anyone who has encountered bugs in a program they are using, even the professionals don't always get every bug - often because they are not aware of the bug before the software is released to the public.
Before now my programs have been short enough that the range of inputs is fairly simple.
As previously discussed, the two main groups of bugs are syntax errors, where the Python Interpreter does not like what is written (one bracket in a pair is missing, or there isn't a colon at the end of the first line of a while, for or if structure, or a string isn't enclosed in quotes, so the program thinks it's a variable), and logical errors where the programmer gives the Python interpreter valid instructions but the results are not what the programmer wants.
Syntax errors in the main program are usually picked up quickly - a lot will be found when I first try to run the code and IDLE points out the errors that prevent it from doing so, usually the first one encountered when it tries to run the program, and so multiple runs and debugs will work through these syntax errors.
Some syntax errors are not exactly within Python but with another software package that Python interacts with - for example if I am using Python to interact with SQLite and I tell the Python cursor object to execute a bad SQLite command, SQLite will send an error back to Python and Python in turn will interrupt the program with this error.
Some syntax errors are more subtle, particularly if they are within functions. These may only appear once the program calls on the function. For example there is an SQLite error because one function (here searchtable()) sends a bad SQLite command via the cursor:
========= RESTART: C:\Users\pc\Documents\Programming\historical_4.py =========
Historical Figures Table
========================
Main menu - Please select an option
View whole table ---V
Search table -------S
Enter new record ---E
Delete record ------D
Update record ------U
Quit Program -------Q
Add/Delete Role ----R
Add/Delete Weblink -W
? s
Searching table - please select which column
Name ----(N)
Born ----(B)
Died ----(D)
Nation --(T)
Role ----(R)
? r
What Role? explorer
Traceback (most recent call last):
File "C:\Users\pc\Documents\Programming\historical_4.py", line 336, in <module>
searchtable()
File "C:\Users\pc\Documents\Programming\historical_4.py", line 240, in searchtable
curs.execute("SELECT * FROM historicalfigures WHERE " + searchterm +";")
sqlite3.Warning: You can only execute one statement at a time.
>>>
This particular one turned out to be an unwanted semicolon, making SQLite think there were 2 commands rather than the one intended.
From my own experience, the best way, perhaps only way, to find these errors is to thoroughly test the program, systematically going through each function and trying different inputs, first valid inputs that the programmer expects from the user, and then (if feeling adventurous), invalid or wrong inputs. Ideally the program should not crash simply because the user makes a mistake.
When doing this I will sometimes generate records that are not intended to be kept - for example adding Mickey Mouse as a historical figure. This is for two reasons - Firstly if I get things wrong (in terms of the program parsing my input incorrectly) then any problematic records can be deleted without regret. Secondly it means I am motivated to use the delete functions, which I use less often than the entry/adding functions.

As an aside, some files and databases may be permanently changed as a result of programs being run, such as this historical database being changed when testing the adding, editing and deleting of records. If the data is important, it may be better to create a test copy of the data and test the program using that, or at least create a backup of both the original data and the original program. Here one of SQLite's features is useful - unlike some SQL databases, SQLite keeps each database in a single file that can be copied and pasted like other files.
I'm not going to go through each error I have discovered in the historical database program - it turns out there were a lot more than I had expected.

Another aspect of testing is seeing things from a user's point of view. While testing this historical database program I have sometimes thought "Wouldn't it be convenient if...." or "Wouldn't it be cool if...". These can give rise to improvements to the program. Those improvements will need testing in due course to ensure new errors have not been introduced.

Monday, 8 October 2018

Changes to the Database of Historical Figures

I have gone into SQLite and done the following:

C:\Users\pc>cd c:\sqlite

c:\sqlite>sqlite3.exe
SQLite version 3.20.1 2017-08-24 16:21:36
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open db/history1.db
sqlite> .tables
historicalfigures
sqlite> create table roles (Name text, Role text);

sqlite> create table weblinks (Name text, weblink text, notes text);

sqlite> .tables
historicalfigures roles weblinks

sqlite> .quit

c:\sqlite>

This is a bit of a change from my original solution - I am creating two tables, one for roles and the other for web addresses. The main reason why weblinks is a new table and not a new column is because I think it would be good if there was the capacity to hold more than one web address for any one historical figure.

I haven't deleted the Roles column in the main table yet - I have plans for that. I want to create a script to automatically transfer the data from that column into the new roles subtable. This is helped by the format of the Roles column because so far each historical figure has either one or two roles, and when there are two they are separated by " & ". This makes automated splitting fairly simple. In fact I've just rustled up a quick script to do this:

import sqlite3

conn = sqlite3.connect("C:\\sqlite\\db\\history1.db")
curs = conn.cursor()

curs.execute("SELECT Name, Role from historicalfigures;")
roledump = curs.fetchall()

commit = False
for row in roledump:
    Name = row[0]
    RawRole = row[1]
    print (Name, RawRole)
    if "&" in RawRole:
        splitroles = RawRole.split(' & ')
    else:
        splitroles = [RawRole]
    for r in splitroles:
        curs.execute("SELECT Name FROM Roles WHERE Name = '"+ Name +"' AND Role = '"+ r +"';")
        checkdump = curs.fetchall()
        if not checkdump:
            print ("Empty!")
            curs.execute("INSERT INTO roles (Name, Role) VALUES ('"+ Name +"', '"+ r +"');")
            commit = True
        else: print("Not unique")
if commit == True:
    curs.execute("COMMIT;")
conn.close()

And it seems to run well. It is a one-off script - I run it once and if it does its job, it is no longer needed. Creating a program that I will only use once may seem excessive. But when the tables become long with many records, it becomes a question of what is more work - entering data into the new table by hand or writing a script like this. Besides, this way is less boring.

Of course, creating these subtables (both roles and weblinks) means that our program will have to be changed considerably to cope with these.

With a bit of research I have found how to use SELECT in SQLite to gather records from one table using results of selecting from another table. This is important for anyone (like me) using a relational database (more than one table, linked to each other).

C:\sqlite>sqlite3
SQLite version 3.20.1 2017-08-24 16:21:36
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open db/history1.db
sqlite> .tables
historicalfigures roles              weblinks
sqlite> SELECT * FROM historicalfigures WHERE Name IN (SELECT Name FROM roles WHERE Role = 'Explorer');
Christopher Columbus|1451|1509|Explorer|Spain & Italy
Ferdinand Magellan|1480|1521|Explorer|Portugal
Francis Drake|1540|1596|Explorer & Naval Commander|England
Marco Polo|1254|1324|Explorer & Trader|Italy (Venice)
sqlite>

That is just SQLite, but it is fairly simple to get Python to execute this sort of thing.

The current version of the historical database program has had to undergo revision due to the change in the database structure. Rather than copying and pasting the whole thing, I'm going to just copy and paste the main program and leave out the various user-defined functions. This is not something I've done before on this blog, but the program is getting big enough that the whole script including functions is too much for one post.
So here's the main program:

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():
….
def addrole():
….
def checkint(num):
….
def deleterecord():
….
def deletelink():
….
def deleterole():
….
def displayrecord(name):
….
def displaytable(dump):
….
def enternew():
….
def searchtable():
….
def updaterecord():
….
def viewtable():
….

#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")

So what's going on here? I've added a commented-out list of the modules and rearranged them in alphabetical order. This is more a matter of human readability than the Python interpreter. This size of program benefits from a bit of organization so that a developer can find the function they want to look at. Does this affect the flow of the program? Not really - the definition of the function (stating what the function does) is not the same as calling it (i.e. telling it to run). The functions still need to be called at the correct point in the program, but the definitions can be in any order as long as they are defined before they are called (hence the definitions are after importing modules but are before the main program gets going).
As before, the active part of the main program is first of all connecting to the database and creating the cursor object, and secondly the options menu that the user interacts with, using if...elif...else.... Each selected option simply runs its associated function. More options have been added due to the change in the database itself - now there is adding/deleting both roles and weblinks.

Tuesday, 2 October 2018

Improvements for the database program

Last post I presented a program run on the Python command line (in my case IDLE) that connected to and interacted with a SQLite database of historical figures.
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.

So the newer script is:
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.