Importing CSV files into SQLite

So this is another diversion from Python itself, but given how Python can interact with both SQLite and CSV files, I think it still fits into this blog's range of topics.

Previously I have debated SQLite vs CSV (Comma Separated Values) and came down on the side of SQLite. However, a lot of data is collected as spreadsheets - a quick and easy way to do tables of data, and tables are at the heart of how SQLite stores data. There is a method.
The example I will be using is for a database of Dungeons and Dragons things, including characters, monsters, spells and magic items. I have a spreadsheet (actually OpenOffice, .ods) from an older project where I painstakingly inputted over 800 spells. Rather than duplicate that effort with data entry in SQLite, it seems better to import the sheet into SQLite

Firstly there needs to be an appropriate SQLite table in the SQLite database. Usually the nature of the system and the problem  means that you are importing into an existing one, but just a warning that you can't import into an SQLite table that doesn't exist yet.
The data for the SQLite table "spell" is show in a program that makes use of the PRAGMA SQLite command.
Table: spell
============
(0, 'SpellName', 'text primary_key', 0, None, 0)
(1, 'Source', 'text', 0, None, 0)
(2, 'CasterClass', 'text', 0, None, 0)
(3, 'SpellLevel', 'integer', 0, None, 0)
(4, 'Description', 'text', 0, None, 0)
Number of records in spell = 2

So now we make sure that the spreadsheet has a similar layout.
Here are some sample rows

Caster/Domain	Spell Name	Level	Brief Info	Source
Air	Obscuring Mist	1	Fog surrounds you	Player's Handbook
Air	Wind Wall	2	Deflects arrows, smaller creatures & gases	Player's Handbook
Air	Gaseous Form	3	Subject becomes insubstantial and can fly slowly	Player's Handbook
Air	Air Walk	4	Subject treads on air as if solid (climb at 45-degree angle)	Player's Handbook
Air	Control Winds	5	Change wind speed and direction	Player's Handbook
Air	Chain Lightning	6	1d6 damage/level, secondary bolts	Player's Handbook

This does not use exactly the same column headings or order, so let's rearrange the columns  in the sheet. We also remove the heading rows (otherwise they will be imported as data). I decided to use semicolons as the delimiter between cells, so I use Find/Replace to remove any confusing semicolons from the data (replacing them with commas). Then we Save As... a CSV file. 
OpenOffice asks a few questions when saving from a spreadsheet to a CSV, including "Do you want to quote text values?" and I said yes. I hope this is right.

So if I now open up the CSV file using a text editor that than a spreadsheet program I get
"Obscuring Mist":"Player's Handbook":"Air":1:"Fog surrounds you"
"Wind Wall":"Player's Handbook":"Air":2:"Deflects arrows, smaller creatures & gases"
"Gaseous Form":"Player's Handbook":"Air":3:"Subject becomes insubstantial and can fly slowly"
"Air Walk":"Player's Handbook":"Air":4:"Subject treads on air as if solid (climb at 45-degree angle)"
"Control Winds":"Player's Handbook":"Air":5:"Change wind speed and direction"
"Chain Lightning":"Player's Handbook":"Air":6:"1d6 damage/level, secondary bolts"
Actually those look suspiciously like colons, not semicolons. But it doesn't matter in this case.
So here comes the meat of the matter. I'm not going to do this in Python but I'll go direct to the SQLite command line using Windows Command Prompt.

Microsoft Windows [Version 10.0.17134.590]
(c) 2018 Microsoft Corporation. All rights reserved.
C:\Users\pc>cd C:\sqlite
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> .separator :
sqlite>
sqlite> .open db/cardshuffle.db
sqlite> .tables
character  charlevel  event      location   magicitem  monster    spell
sqlite> .import C:/Users/pc/Documents/Misc D&D/SpellList2.csv spell
Usage: .import FILE TABLE
sqlite> .import "C:/Users/pc/Documents/Misc D&D/SpellList2.csv" spell
sqlite>

This is rather arcane knowledge (no, not the D&D spells, but the SQLite command line interface).
These are not commands that are executable via Python - they are SQLite interface commands.
So now when I run the same program as I did to show the metadata for the columns, I get:
Table: spell
============
(0, 'SpellName', 'text primary_key', 0, None, 0)
(1, 'Source', 'text', 0, None, 0)
(2, 'CasterClass', 'text', 0, None, 0)
(3, 'SpellLevel', 'integer', 0, None, 0)
(4, 'Description', 'text', 0, None, 0)
Number of records in spell = 814

The bottom line shows that there are now 814 records, and since there were 812 lines in the CSV file, I feel confident it has been imported as intended.

Let's try this again with the monsters table.  The metadata/PRAGMA for this is:
Table: monster
==============
(0, 'MonName', 'text primary_key', 0, None, 0)
(1, 'Source', 'text', 0, None, 0)
(2, 'CR', 'integer', 0, None, 0)
(3, 'MonType', 'text', 0, None, 0)
(4, 'Description', 'text', 0, None, 0)
(5, 'Image', 'text', 0, None, 0)
Number of records in monster = 2

And the first few lines of the spreadsheet are:

Name	Type	Basic HD	CR	Alignment	Source
Spectral Plant	Plant	0	0		CC2: DM
Bat	Animal	0.25	0.1	TN	MM
Toad	Animal	0.25	0.1	TN	MM
Floating Eye	Beast	0.5	0.1	TN	Tome of Horrors

Here the columns don't quite match up. In the spreadsheet, Name, Type, CR and Source are the equivalent of the SQLite table columns of MonName, MonType, CR and Source. However, the SQLite table has no column for Alignment or for HD, while the spreadsheet has no column for Description or Image. Also CR in the spreadsheet can be a decimal fraction (effectively a floating point number) while in the SQLite table it must be an integer (whole number).

Can this be done? Should it be done? These are judgements that should be made by the developer and maybe end user. Let's go ahead.
So I shuffle the columns around, remove the unwanted header row, round the floating point numbers down to integers, delete the unwanted columns and save it as a CSV file. Opening it up in a text editor, the first few lines are:
"Spectral Plant";"CC2: DM";0;"Plant"
"Bat";"MM";0;"Animal"
"Toad";"MM";0;"Animal"
"Floating Eye";"Tome of Horrors";0;"Beast"
"Centipede, Tiny";"MM";0;"Vermin"
"Rat";"MM";0;"Animal"

So I try importing this on the SQLite command line interface.
Microsoft Windows [Version 10.0.17134.590]
(c) 2018 Microsoft Corporation. All rights reserved.
C:\Users\pc>cd C:\sqlite
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/cardshuffle.db
sqlite> .tables
character  charlevel  event      location   magicitem  monster    spell
sqlite> .separator ;
sqlite> .import "C:/Users/pc/Documents/Misc D&D/MonsterList.csv" monster
C:/Users/pc/Documents/Misc D&D/MonsterList.csv:1: expected 6 columns but found 4 - filling the rest with NULL
C:/Users/pc/Documents/Misc D&D/MonsterList.csv:2: expected 6 columns but found 4 - filling the rest with NULL
C:/Users/pc/Documents/Misc D&D/MonsterList.csv:3: expected 6 columns but found 4 - filling the rest with NULL
C:/Users/pc/Documents/Misc D&D/MonsterList.csv:4: expected 6 columns but found 4 - filling the rest with NULL
….
sqlite>

Actually it produces a lot more warning messages - not error messages, because it is still partially completing the tasks and continuing with the overall process.  

And the results (still in SQLite command line interface)?

sqlite> .headers
Usage: .headers on|off
sqlite> .headers on
sqlite> .mode columns
sqlite> SELECT * FROM monster WHERE MonType ="Giant";
MonName     Source      CR          MonType     Description  Image
----------  ----------  ----------  ----------  -----------  ----------
Rock Troll  Drag286     1           Giant
Ogre        MM          2           Giant
Giant, Pha  Faerun      3           Giant
Troll, Ice  Tome of Ho  3           Giant
Troll, Swa  Tome of Ho  3           Giant
Celestian   CC          4           Giant
Tuscar      CC2: DM     4           Giant
Troll, For  Mon Man 3   4           Giant
Steppe Tro  CC          5           Giant
Fensir      Fiend Foli  5           Giant
Ettin       MM          5           Giant
Troll       MM          5           Giant
Ogre, Skul  Mon Man 3   5           Giant
Giant, Woo  Tome of Ho  5           Giant
Troll, Cav  Tome of Ho  5           Giant
Aberrant    Tome of Ho  5           Giant
Flayed Gia  CC2: DM     6           Giant
Surged Gia  CC2: DM     6           Giant
Giant, Bog  Fiend Foli  6           Giant
Troll, Roc  Tome of Ho  6           Giant
Troll, Two  Tome of Ho  6           Giant
Giant, Smo  Tome of Ho  6           Giant
Qor         Liber Best  7           Giant
Giant, Hil  MM          7           Giant
Troll, Cry  Mon Man 3   7           Giant
Gaurak Tro  CC2: DM     8           Giant
Giant, Fog  Faerun      8           Giant
Fensir, Ra  Fiend Foli  8           Giant
Giant, Sto  MM          8           Giant
Ogre Mage   MM          8           Giant
Ogre, Skul  Mon Man 3   8           Giant
Troll, Cav  Mon Man 3   8           Giant
Steel Gian  CC          9           Giant
Half-Troll  Fiend Foli  9           Giant
Giant, Fro  MM          9           Giant
Giant, Cav  Tome of Ho  9           Giant
Stick Gian  CC          10          Giant
Forest Wal  CC2: DM     10          Giant
Giant, Fir  MM          10          Giant
Giant, San  Mon Man 3   10          Giant
Troll, Mou  Mon Man 3   10          Giant
Giant, Sea  Tome of Ho  10          Giant
Giant, Clo  MM          11          Giant
Fomorian    Mon Man 2   11          Giant
Giant, For  Mon Man 2   11          Giant
Giant, Bro  Tome of Ho  11          Giant
Firbolg     Mon Man 2   12          Giant
Giant, Sun  Mon Man 2   12          Giant
Biclops     Tome of Ho  12          Giant
Giant, Sto  MM          13          Giant
Menkau (Sa  Necropolis  14          Giant
Giant, San  Tome of Ho  14          Giant
Giant, Vol  Tome of Ho  14          Giant
Giant, Eld  Mon Man 3   15          Giant
Giant, San  Mon Man 3   15          Giant
Biclops He  Tome of Ho  15          Giant
Giant, Dea  Mon Man 3   16          Giant
Giant, Fer  Tome of Ho  17          Giant
Giant, Jac  Tome of Ho  17          Giant
Giant, Sha  Fiend Foli  18          Giant
Giant, Oce  Mon Man 2   19          Giant
Geriviar    Mon Man 3   19          Giant
Giant, Eld  Mon Man 3   20          Giant
Giant, Mou  Mon Man 2   26          Giant
SQLite>

That's good enough for me. I can fill in the other columns as and when.

Database Programs - the Generic and the Specific

So I have been working on a Fighting Fantasy project. Before I or anyone can play the game I need to input the data for both paragraphs and monsters. Currently the SQLite database (fight.db) has 4 tables, each with a number of columns.
monster table

• name (text, primary key)
• skill (integer)
• stamina (integer)
• treasure (integer)

equip table

• monname (text, primary key)
• equipname (text, primary key)
• equiptype (text)
• lootable (integer)
• adjust (integer)

paragraph table

• module (text, primary key)
• pararef (text, primary key)
• paratext (text)
• monster (text)
• looseitem (text)

options table

• module (text, primary key)
• startpararef (text, primary key)
• endpararef (text, primary key)
• optiontext (text)
• conditions (text)

I won't go into a detailed explanation of each column, suffice to say there are a mix of text and integers, and some fields are primary keys.
For those who are not familiar with primary keys, these are columns that are used to single out and identify each record. If a table has a single column as its primary key, each record in the table must have a unique value for that column. So for example, in the monster table there is one column as the primary key, name. Each entry must have a unique name value. If a table has 2 or more columns as primary keys, then the combination of those columns must be unique. For example, an entry in the paragraph table must have a unique combination of module column and pararef column.

To add new records, delete unwanted records and edit existing records in all four tables I have created two different programs using two different approaches. The first one took quite an abstract approach, treating the tables as unrelated and not really taking into account the individual needs of each table or each column. The first program uses the PRAGMA command in SQLite to retrieve information about each table, namely which columns are primary keys and also which are text or numbers. This means that the program won't try to input text values into columns for integers. It should also catch duplicate primary keys. The good news for this approach is that it is very adaptable, and if I change the tables or even use it on a different database it should still work. The program itself is 263 lines of code & comments

The second one is much more customised and takes into account the relationships between tables.
Although in Python a variable can represent a list, tuple or dictionary (a series of different values, called an iterable), in SQLite they don't use lists or other iterable datatypes - you create more tables and elements that would be in the list become records in the new table.
So if a monster has several items of equipment associated with it, rather than having a column called equip, there is a separate table. One column (here monname) says what monster record each item in the table is associated with. Here the value in monname should match up with a value in the name column of the monster table. Another column (here equipname) identifies each item associated with that monster. It is no coincidence that monname and equipname are the two columns that form the primary key for this table - the combination of monname and equipname should be unique and identify each entry in the equip table.
This is a similar situation to paragraphs and options - each paragraph typically has several options associated with it, though each option has its own characteristics associated with it.

So when entering a new paragraph, the program will ask whether the user also wants to enter options associated with that paragraph. Similarly, when deleting an unwanted paragraph, the program will offer to delete options associated with that paragraph (where startpararef value in options matches pararef value in paragraph).  Also if the primary key (pararef) value of a paragraph record is changed, the program offers to update the startpararef value of options associated with the paragraph.  These functions are also adapted to the situation with monsters and associated equipment.

This customised version is a lot longer at 665 lines - as well as writing functions that can cope with any table, I have also written functions to deal with specific tables. Is it better? When I am using it to add data to the database, yes I believe so. But I have to hope that I won't be changing the SQLite table structure because the second version assumes the database is set up in a particular way.
Incidentally, both versions can present the contents of each table in HTML. The first one can do so in a straightforward, no-frills way.

Current Contents of paragraph

module	pararef	paratext	monster	looseitem
haunted	001	The storm is raging, and you are pelted by rain and hail. You take shelter in the porch of an old creaking house. Perhaps you can find food and a bed for the night? You knock on the door, but it is not locked and swings open. You consider going in or looking around the outside of the house.
haunted	001a	You find yourself at the front of the house. The front door is still ajar. There are stretches of garden either side to the north and south.
haunted	002	You walk around to the north side of the house. The windows on the ground floor are all boarded up and you cannot see in. Through the pelting rain you can see what may be a vegetable garden further to the north. The garden continues along the side of the house.
haunted	003	Walking around the south side of the house you come across a large pond. In the middle is a statue of what may be a satyr or a goatish demon. The water in the pond looks murky with pondweed. The south side of the house continues ahead.
haunted	003a	As you step into the cold murky water the mud covers your ankles. You move in, towards the mysterious statue. You suddenly see from the corner of your eye something moving in the water towards you. Something dark green, slimy and with long limbs lurches at you from under the water. You must defend yourself against the giant frog!	Giant frog
haunted	005	You approach the vegetable patch, and notice some figure shuffling around behind the canes supporting runner beans. Covered in rags and wielding a pitchfork it lurches towards you. As you see its rotting face, you realize the gardener is an undead zombie and you must fight it!	Zombie
haunted	005a	You approach the vegetable patch. The remains of a defeated zombie are scattered over the cabbages and broccoli. Just to the west you can see a surprisingly large garden shed.

The second one has the option of including the options associated with each paragraph:

Current Contents of Paragraph & Options table

module	pararef	paratext	monster	looseitem	options
haunted	001	The storm is raging, and you are pelted by rain and hail. You take shelter in the porch of an old creaking house. Perhaps you can find food and a bed for the night? You knock on the door, but it is not locked and swings open. You consider going in or looking around the outside of the house.			('Do you go left around the north side of the house?', '', '002') ('Do you go right around the south outside of the house?', '', '003') ('Do you step into the dark house?', '', '004')
haunted	001a	You find yourself at the front of the house. The front door is still ajar. There are stretches of garden either side to the north and south.			('Head left to the north side of the house?', '', '002') ('Head right to the south side of the house?', '', '003') ('Push open the door and go into the house?', '', '004')
haunted	002	You walk around to the north side of the house. The windows on the ground floor are all boarded up and you cannot see in. Through the pelting rain you can see what may be a vegetable garden further to the north. The garden continues along the side of the house.			('Go over to the gardening figure in the vegetable patch?', 'Alive:Zombie', '005') ('Carry on along the north side of the house?', '', '006') ('Head back east to the front door?', '', '001a') ('Head over to the vegetable patch to the north?', 'Dead:Zombie', '005a')
haunted	003	Walking around the south side of the house you come across a large pond. In the middle is a statue of what may be a satyr or a goatish demon. The water in the pond looks murky with pondweed. The south side of the house continues ahead.			('Wade into the pond to investigate the statue?', 'Alive:Killer frog', '003a') ('Wade into the pond to investigate the statue?', 'Dead:Killer frog', '003b') ('Head back east round to the front door?', '', '001a')
haunted	003a	As you step into the cold murky water the mud covers your ankles. You move in, towards the mysterious statue. You suddenly see from the corner of your eye something moving in the water towards you. Something dark green, slimy and with long limbs lurches at you from under the water. You must defend yourself against the giant frog!	Giant frog
haunted	005	You approach the vegetable patch, and notice some figure shuffling around behind the canes supporting runner beans. Covered in rags and wielding a pitchfork it lurches towards you. As you see its rotting face, you realize the gardener is an undead zombie and you must fight it!	Zombie
haunted	005a	You approach the vegetable patch. The remains of a defeated zombie are scattered over the cabbages and broccoli. Just to the west you can see a surprisingly large garden shed.

This gives a better idea of how the records in different tables relate to each other.

A Script to Look at Database Files

This program arose to make things easy for me in the long run, a kind of constructive laziness (as discussed here). There are times when I am writing programs that use database files, and I forget what the table names are, or what the column names and types are, and SQLite needs these to be correctly spelt (and is case sensitive). I could open up sqlite3 on the Windows command line and try to remember how to view the metadata about the database, but that can be a bit of hassle, particularly when my mind is focused on Python code.
This program looks at that metadata and presents it in a relatively easy way.

import sys
import os
import webbrowser
import sqlite3
# Collecting list of database files
dirlist = os.listdir('C:\\sqlite\\db')
dbfilelist = []
filecount = 0
for file in dirlist:
    if file[-3:] == '.db':
        print(filecount, file, 'found')
        dbfilelist.append(file)
        filecount +=1
print(filecount, 'Completely different directory')
# Selecting which db file
filechoice = input("Enter number of file to view: ")
try:
    fileno = int(filechoice)
    chosenfile = dbfilelist[fileno]
    print(chosenfile, 'selected')
    dirpath = 'C:\\sqlite\\db\\'+chosenfile
except:
    try:
        if int(filechoice) == filecount:
            dirpath = input("Please enter new directory path including filename: ")
            # Check that new dirpath is valid file
            fcheck = open(dirpath, 'r')
            fcheck.close()
            chosenfile = dirpath
    except:
        print("Sorry, cannot parse that.")
        sys.exit()
# Starting up SQLite and selecting tables
conn  = sqlite3.connect(dirpath)
curs = conn.cursor()
curs.execute('SELECT name FROM sqlite_master WHERE type="table";')
tabdump = curs.fetchall()
tabcount = 0
print("Current tables in", chosenfile)
if len(tabdump) == 0:
    print("Database seems to be empty - quitting")
    sys.exit()
for table in tabdump:
    print(tabcount, table[0])
    tabcount += 1
tabchoice = input("Select which table to inspect or Q to quit: ").lower()
if tabchoice == 'q':
    print("Quitting - bye!")
    sys.exit()
else:
    tabname = tabdump[int(tabchoice)][0]
    print(tabname, "table info")
    curs.execute("PRAGMA table_info("+tabname+");")
    pragdump = curs.fetchall()
    collist = []
    for line in pragdump:
        print(line)
        collist.append(line[1])
    curs.execute("SELECT * FROM "+tabname+";")
    contentsdump = curs.fetchall()
    rows = len(contentsdump)
    print(tabname, "contains ", rows, "records/rows")
# Creating HTML table   
if rows > 0 and input("View table as HTML (Y)? ").lower() == 'y':
    FH = open('temp.html', 'w')
    FH.write(" \n")
    FH.write("<HTML> <BODY>\n")
    FH.write("<TABLE Border=1> <TR>")
    for col in collist:
        FH.write("<TD><B>"+col+"</B></TD>")
    for line in contentsdump:
        FH.write("<TR> \n")
        for cell in line:
            FH.write("<TD>"+str(cell)+"</TD>")
        FH.write("</TR> \n")
    FH.write("</TABLE> \n")
    FH.write("</BODY> </HTML> \n")
    FH.close()
    webbrowser.open('temp.html')
   
conn.close()


This script works well and makes relatively few assumptions. As a matter of good habit, I create and keep database files in 'C:\SQLite\db' folder in Windows, and I always give them a '.db' extension at the end of the filename. This script assumes the user wants that folder, but the user can go for a completely different directory path. This option also allows for users to try to open files without the .db extension.  The os module proved useful in listing files.
The sys module was used to exit gracefully in a number of situations where the program could not progress.
The tables within each database can be found by looking at the sqlite_master table, which holds other information as well.
The SQLite PRAGMA command was useful in getting information about a table in the database, including the columns in the table.
Finally there is the option of viewing an HTML version of the table, using a similar method to that used before in my previous programs.
Typical results are shown here:

= RESTART: C:/Users/666/Dropbox/Misc Programming/Python/python3/db_viewer.py =
0 base1.db found
1 biology.db found
2 dragon.db found
3 example.db found
4 scimitar.db found
5 Completely different directory
Enter number of file to view: 0
base1.db selected
Current tables in base1.db
0 MonstrousTroops
1 loot
Select which table to inspect or Q to quit: 0
MonstrousTroops table info
(0, 'Name', 'TEXT', 0, None, 0)
(1, 'Challenge', 'REAL', 0, None, 0)
(2, 'Army', 'TEXT', 0, None, 0)
(3, 'Notes', 'TEXT', 0, None, 0)
MonstrousTroops contains  29 records/rows
View table as HTML (Y)? n

Historical Database ver5 - combining SQLite, Python and HTML

I have had a go at updating the program for looking at and managing the database of historical figures. Last post I was busy altering the SQLite database to add a new column (imagelink, for images associated with each record) and remove an unwanted column (Role, which has been replaced by a subtable to allow multiple roles per personality).

Now I have been modifying the Python program firstly to accommodate the changes in the data structure, and also to display any selected record as a HTML page. This is for two reasons:
Firstly HTML pages can display images when opened in a browser; Secondly HTML can take web links in the database and turn them into clickable hyperlinks. You can't do either of these just on the IDLE command line.(A third reason that isn't put into practice here is HTML makes tables easier, particularly automating column width - useful when you want to display data in tabular form).

 The whole program is now 415 lines long, so I won't just copy and paste the whole thing. That's one of the things I've noticed since starting out learning Python. When you keep working on a program, it can grow surprisingly big. The trick is to make sure that big does not mean unreadable or unmanageable (functions really help here, as do comments in the code).
Making changes to the data structure can mean lots of changes to Python code, and I've been going through the code and testing it, seeing what the ramifications of changing the columns are for the code. This has affected editing existing records, entering new records, displaying the main table and of course displaying records.

This is the function that has changed the most, the one for displaying individual records.

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]
    FileH = open("temp.html", 'w')
    FileH.write("<HTML>")
    FileH.write("<h3>Name: " + name +"</h3> \n")
    FileH.write("<p>Born: " + str(mainrecord[1])+"</p> \n")
    FileH.write("<p>Died: " + str(mainrecord[2])+"</p> \n")
    FileH.write("<p>Nation: " + mainrecord[3]+"</p> \n")
    FileH.write("<p>Image: " + str(mainrecord[4])+"</p> \n")
    FileH.write("<br><img src='"+str(mainrecord[4])+"' width=300 align='right'>")
    rolestring = "<p><B>Roles</B></p> <ul>\n"
    for line in rolesdump:
        rolestring = rolestring + "<li>"+ line[1] +"</li> \n"
    rolestring += "</ul> \n"
    FileH.write(rolestring)
    if webdump: # does webdump contain any lines?
        FileH.write("<p><b>Web addresses for more info</b></p> \n")
        FileH.write("<ul>")
        for line in webdump:
            FileH.write("<li><a href ='"+line[1]+"'>"+line[2] +"</a></li> \n")
        FileH.write("</ul>")
    FileH.write("</HTML>")
    FileH.close()
    webbrowser.open("temp.html")


As you can see it first collects the data from SQLite (using the curs cursor object as usual), then creates a file (temp.html) and file handle (a.k.a. io stream) so Python can write to it, and then writes HTML code to the file modified by what the data in the database is. Finally it uses the webbrowser module (imported along with sqlite3 module earlier in the code) to open the newly created web page in your web browser.

Here is the HTML contents of a typical record:

<HTML><h3>Name: Cardinal Thomas Wolsey</h3>
<p>Born: 1473</p>
<p>Died: 1530</p>
<p>Nation: England</p>
<p>Image: https://collectionimages.npg.org.uk/large/mw06903/Thomas-Wolsey.jpg</p>
<br><img src='https://collectionimages.npg.org.uk/large/mw06903/Thomas-Wolsey.jpg' width=300 align='right'><p><B>Roles</B></p> <ul>
<li>Politician</li>
<li>Cleric</li>
</ul>
</HTML>

Any web designer will tell you this is not exactly attractive, but I believe it serves as proof of concept - we can use Python to get data from a database and create a custom webpage using that data.

Historical Databases, Copying Tables and Adding Columns to SQLite

So I have decided to go back to an older project, the historical database I used to demonstrate the basics of SQLite.
I have decided the next version should have images associated with each record. At the moment the database does not have this, so how do we modify the columns of the database?

There is the SQLite command ALTER. Because I tend to leave the data structure (table columns and how tables relate to each other) alone in Python, this is going to be a one-off, done on the SQLite command line not as part of a Python script.

Microsoft Windows [Version 10.0.17134.407]
(c) 2018 Microsoft Corporation. All rights reserved.
C:\Users\pc>cd C:\sqlite
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> ALTER TABLE historicalfigures ADD COLUMN imagelink text;
SQLite>
sqlite> .mode columns
sqlite> .headers on
sqlite> SELECT * FROM historicalfigures;
Name               YearBirth   YearDeath   Role        Nation      imagelink
-----------------  ----------  ----------  ----------  ----------  ----------
Leonardo da Vinci  1452        1519        Artist      Italy
Niccolo Machiavel  1469        1527        Politician  Italy (Flo
Cardinal Thomas W  1473        1530        Churchman   England
William Caxton     1422        1491        Publisher   England
William Shakespea  1564        1616        Author & P  England
Francis Drake      1540        1596        Explorer &  England
Henry VIII         1491        1547        Monarch     England
Elizabeth I        1533        1603        Monarch     England
Christopher Colum  1451        1509        Explorer    Spain & It
Ferdinand Magella  1480        1521        Explorer    Portugal
Dante Alighieri    1265        1321        Author & P  Italy (Flo
Michelangelo di L  1475        1564        Artist      Italy (Flo
Henry VII          1457        1509        Monarch     England
Hans Holbein       1497        1543        Artist      Germany &
Anne Boleyn        1501        1536        Queen Cons  England
Geoffrey Chaucer   1343        1400        Poet & Aut  England
Marco Polo         1254        1324        Explorer &  Italy (Ven
Catherine of Arag  1485        1536        Queen Cons  Spain & En
Edward VI          1537        1553        Monarch     England
Galileo Galilei    1564        1642                    Italy (Tus
Johannes Kepler    1571        1630                    Germany
Jane Grey          1536        1554                    England
Martin Luther      1483        1546                    Germany
Nicolaus Copernic  1473        1543                    Poland
Tomas de Torquema  1420        1498                    Spain
Donald Duck        1910        2000                    USA
sqlite>

This shows that the new column imagelink has been created (clearly no data yet). But it also reminds me that we have a redundant column - Role column has been superseded by the roles table.
How do we delete this column? Firstly I need to be sure that deleting it is the right thing to do. Deleting columns containing data should not be undertaken lightly.
I have discovered a way to create copies of tables in SQLite3, so I can use these to create backup tables. It involves the CREATE command, but rather than stating all the columns and their data types (which would create an empty table) I tell SQLite3 to create using an existing table (which both uses the columns with names and data types, and also uses the data contained within the table) using the format:
CREATE TABLE newtablename AS SELECT requiredcolumns from existingtablename (parameters); 

 sqlite> CREATE TABLE old_historicalfigures AS SELECT * from historicalfigures;
sqlite> .tables
historicalfigures      roles
old_historicalfigures  weblinks
SQLite>

Just for reference I also found a way to rename tables.

sqlite> ALTER TABLE historicalfigures RENAME TO old_historicalfigures;
But I digress. We now have backed up our table, so can mess around with it with less trepidation.
I have now found out there is no easy way to actually delete or drop an unwanted column in SQLite. Other forms of SQL will allow this. The best workaround is to recreate the table without the unwanted column. Actually this is not too bad - we've already discovered how to do this.

To delete a whole table
sqlite> DROP TABLE historicalfigures;
sqlite> .tables
old_historicalfigures  roles                  weblinks
SQLite>
Now we recreate the table we want with the columns we want.
sqlite> CREATE TABLE historicalfigures AS SELECT Name, YearBirth, YearDeath, Nation, Imagelink FROM old_historicalfigures;
sqlite> SELECT * FROM historicalfigures;
Name               YearBirth   YearDeath   Nation      imagelink
-----------------  ----------  ----------  ----------  ----------
Leonardo da Vinci  1452        1519        Italy
Niccolo Machiavel  1469        1527        Italy (Flo
Cardinal Thomas W  1473        1530        England
William Caxton     1422        1491        England
William Shakespea  1564        1616        England
Francis Drake      1540        1596        England
Henry VIII         1491        1547        England
Elizabeth I        1533        1603        England
Christopher Colum  1451        1509        Spain & It
Ferdinand Magella  1480        1521        Portugal
Dante Alighieri    1265        1321        Italy (Flo
Michelangelo di L  1475        1564        Italy (Flo
Henry VII          1457        1509        England
Hans Holbein       1497        1543        Germany &
Anne Boleyn        1501        1536        England
Geoffrey Chaucer   1343        1400        England
Marco Polo         1254        1324        Italy (Ven
Catherine of Arag  1485        1536        Spain & En
Edward VI          1537        1553        England
Galileo Galilei    1564        1642        Italy (Tus
Johannes Kepler    1571        1630        Germany
Jane Grey          1536        1554        England
Martin Luther      1483        1546        Germany
Nicolaus Copernic  1473        1543        Poland
Tomas de Torquema  1420        1498        Spain
Donald Duck        1910        2000        USA
sqlite>
sqlite>

And so we have added a new column, and not so much removed an unwanted column as recreated the table without the unwanted column.
Next time I hope to tie this in with Python programming.

A Fighting Fantasy Combat Tournament and the continue keyword

So I have been working a bit more on the Fighting Fantasy combat thing.  The latest version has two major changes.
First of all it has a tournament system for 2, 4, 16 or 32 combatants. The surviving combatants keep loot, treasure and current stamina score (no time to rest up and heal) from one stage to the next. This is made easier by keeping both combatants and items as objects.

Secondly it uses SQLite for storing data about combatants and items. There are two tables in the fight.db database, one for combatants (the monster table) and one for items (equip table). To tell the program which combatants start with which equipment is down to a column in the items' table with the initial owner's name. The program then turns the data from these tables into objects using the monster and item classes.
The monster class (for combatants) starts with the __init__ method which creates new monster objects. You can see that although the straightforward integer and string attributes are passed as arguments to the __init__ method, the equip attribute, which is a list of equipment objects associated with that monster object, refers back to the database to find records in the equip table with the monster object's name, then uses those records to create new equipment objects.  The __init__ method also then takes the adjustments (adjust attribute) from newequip and applies them to the monster's armor or damage attribute according to equiptype.

class monster:
    def __init__(self, name, skill, stamina, treasure): # You need to include self
        self.name = name
        self.skill = skill
        self.stamina = stamina
        self.treasure = treasure
        self.status = 'Full Health'
        self.equip = []
        self.armor = 0
        self.dam = 0
        curs.execute("SELECT * FROM equip WHERE monname = '"+self.name+"';")
        equipdump = curs.fetchall()
        for line in equipdump:
            newequip = equipment(line[0], line[1], line[2], line[3], line[4])
            self.equip.append(newequip)
            if newequip.equiptype == "weapon":
                self.dam += newequip.adjust
            elif newequip.equiptype == "armour":
                self.armor += newequip.adjust

Because entering and editing data straight into SQLite databases is not very user-friendly, I wrote a second program to manage the database - the program that runs the combat and tournament only reads from the database.

One new keyword I've found useful is continue. This is used in loops and is a counterpart to break. Whereas break tells the program to leave the loop and carry on with the code immediately afterwards, continue tells Python to go back to the start of the while loop rather than carrying on with the rest of the indented code.
This is my using continue in context. I want the user to choose how many combatants are in the tournament and there are several criteria for valid input:

while goodselect == False:
    print("There are "+str(len(monlist))+" combatants ready and waiting")
    quantcombat = input("Do you want 2, 4, 8 or 16 combatants? ")
    try:
        quantcombat = int(quantcombat)
        quanttuple = (2, 4, 8, 16)
    except:
        print("Sorry, not a number!")
        continue
    if quantcombat not in quanttuple:
        print("Sorry, not a suitable tournament number")
        continue
    elif quantcombat in quanttuple:
        print("you have selected "+str(quantcombat)+" combatants")
        stages = quanttuple.index(quantcombat)
        goodselect = True

The result is that until the user inputs an integer that is in quanttuple, Python will go back to the top of the while loop.
I have decided not to copy and paste all the code in the program into this blog, as this is getting quite big (217 lines for the tournament program, 144 lines for the database manager).
As well as writing the program, I have done some data entry - currently 20 combatants, each with 1 weapon and 1 armour (either natural or lootable).
One thing I have realised since starting this is that this SQLite database could be used by other programs involving Fighting Fantasy combat, and I believe I may use the same database for a proper adventure that combines the combat and items system here with the area and choosing options system of my previous project.

Random thoughts on CSV files

Why bother with SQLite when I could use CSV files?
Since restarting this blog a month ago all of my posts have involved SQLite to varying degrees. For those who are expecting to just learn about Python this may be an annoying side-trek. One alternative I discussed in an earlier post is using CSV or text files to hold persistent data. Python can open, read from and write to these files. There are a number of advantages that SQLite has.

• SQLite is faster when file sizes and quantities of data increase. In terms of speed, SQLite is something of a compromise. For enterprise/corporation level databases, something like Oracle or Microsoft SQL Server are in a whole different league of performance and can cope with millions of records and thousands of tables (see this page for MS SQL Server). Nonetheless, SQLite is better than using Python manipulating CSV files, and it is easier for me to install and run than the enterprise-level SQL programs. 
• SQLite does a lot of work that you would otherwise need to do in Python. This is something I've appreciated even at this relatively low level. One well-written SQLite command passed by the cursor object can replace a dozen lines of Python code with their associated possible errors. As stated before, SQL is a language itself, and brings with it extra capabilities for managing and searching databases, especially relational databases. The effort put into learning the basics of SQL starts to pay off quickly. 
• SQLite is professionally more useful, mainly because it uses SQL and a lot of businesses use SQL-based programs and databases rather than CSV files. They might use a different SQL program (like those mentioned above) but the SQL is mostly the same.  
• Editing data in SQLite is far easier than reading/writing to CSV files, particularly if there is one or two items of data to edit in the middle of the table but other data around it does not need to be changed. For SQLite you can use the UPDATE command. For CSV files it is not easy to rewrite a specific line while leaving the rest of the file alone. At my (limited) skill I would end up reading the whole file into Python, making modifications in Python, then overwriting the entire file with the new version held in Python. Although doable with small files, when you are dealing with big files this can become very inefficient. 

Having said all that there are modules that help Python to interact with CSV files, including the csv module documented here. I probably ought to learn these, but right now SQLite seems better, particularly now I've made the initial investment of time and effort in getting SQLite running in my Python programs. CSV files can also be opened and edited in spreadsheets, unlike SQLite files, thus partially nullifying my final bullet point.
And SQLite, being a language with many nice features, can import data from and export data to CSV files, so if you change your mind after entering big tables of data you can always move your data from one to the other.

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.