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.

Fighting Fantasy Combat

This is a project inspired by combining two previous posts, namely the introduction of objects and classes, and the creation of a simple adventure game.

In the Fighting Fantasy gamebooks that inspired the latter there is a simple combat system done using two dice, pencil and paper. Each opponent has a Skill score (typically 4-8) and a Stamina score - Stamina is variable and can decrease with damage. When a combatant has 0 stamina they are dead. When combat ensues:

• Each combatant rolls 2x 6-sided dice and adds the result to their skill score. 
• If the total scores for each combatant are equal that round is a draw and no damage is inflicted. 
• If one combatant has a higher total score than the other, then that combatant inflicts 2 stamina damage on the opponent. 
• This continues until one combatant reaches 0 stamina or below and dies.   

This is a very simple system and I have added to it. Each combatant now has a damage score. This is not the exact amount inflicted but the maximum in a range of integers.
Also each combatant has an armor score (usually 0, 1 or 2) which mitigates damage received from opponents. Thus if a combatant wins a round, damage inflicted on an opponent is between 1 and damage score - opponent's armor score. 
So that's how it works with pencil, paper and dice.
Just to keep things interesting, each combatant may have a treasure score and also may have some items of equipment. When they are defeated, the treasure and equipment are added to the opponent's treasure and equipment (so combatants kill each other then take their stuff).
In terms of Python it might have been doable as lists but this seems to be a good opportunity for object-oriented programming. I've been a bit lazy when it comes to data storage - the data for each combatant is stored within the Python code. This might not be ideal (certainly telling Python to write new data is not feasible) but at this level of simplicity I can get away with it. This program is not purely OOP - it seemed convenient to have a mix of OOP and functional programming, and Python allows me to do that.
Here is the program itself:

import random
import sys
class monster:
    def __init__(self, name, skill, stamina, armor, dam, treasure, equip): # You need to include self
        self.name = name
        self.skill = skill
        self.stamina = stamina
        self.armor = armor
        self.dam = dam
        self.treasure = treasure
        self.status = 'Full Health'
        self.equip = equip
    def display(self): # Again you need to include self
        print("Name    : ", self.name)#Attributes of objects need to say what object they belong to
        print("Skill   : ", str(self.skill))
        print("Stamina : ", str(self.stamina))
        print("Armor   : ", str(self.armor))
        print("Damage  : ", str(self.dam))
        print("Treasure: ", str(self.treasure))
        equipstring = ', '.join(self.equip)
        print("Equip   : ", equipstring)
    def attackroll(self):
        dice1 = int(random.randint(1, 6))
        dice2 = int(random.randint(1, 6))
        #print(self.name, "rolls", dice1, dice2)
        totalattack = dice1 + dice2 + self.skill
        attackstring =  self.name + ' rolls '+ str(dice1) +', '+str(dice2)+ ' + ' + str(self.skill) + ' skill = ' + str(totalattack)
        return totalattack, attackstring
    def dealdamage(self):
        rolldam = random.randint(1, self.dam)
        print(self.name, 'deals', rolldam, 'damage')
        return rolldam
    def damaged(self, dealt):
        dealt = dealt - self.armor
        if dealt < 0: dealt = 0
        self.stamina = self.stamina - dealt
        if self.stamina < 1:
            self.status = "is dead"
        else:
            self.status = "has "+str(self.stamina)+ " Stamina left"
    def picksitems(self, itemlist):
        self.equip += itemlist
        print(self.name, 'has picked up', str(itemlist))
        equipstring = (', '.join(self.equip))
        print(self.name, 'equipment is', equipstring)
    def picksmoney(self, money):
        self.treasure += money
        print(self.name, 'has picked up', str(money))
        print(self.name, 'now has treasure: ', str(self.treasure))
    def dropsitems(self):
        droplist = []
        for i in self.equip:
            droplist.append(i)
        self.equip = []
        print(self.name, 'drops ', str(droplist))
        return droplist
    def dropsmoney(self):
        dropcash = self.treasure
        self.treasure = 0
        print(self.name, 'drops', str(dropcash))
        return dropcash
   
m1 = monster("Orc", 5, 5, 1, 4, 3, ['shortsword', 'shield'])
m2 = monster("Minotaur", 8, 8, 1, 6, 12, ['battleaxe'])
m3 = monster("Giant Spider", 6, 4, 0, 3, 0, [])
m4 = monster("Werewolf", 8, 7, 1, 5, 10, [])
m5 = monster("Dragon", 9, 15, 2, 8, 100, [])
m6 = monster("Dwarf", 7, 7, 2, 4, 10, ['chainmail', 'shortsword'])
m7 = monster("Elf", 8, 5, 1, 6, 10, ['leatherarmour', 'longsword'])
m8 = monster("Killer Weasel", 6, 4, 1, 5, 0, [])
m9 = monster("Assassin", 8, 6, 1, 6, 10, ['leatherarmour', 'scimitar'])
m10 = monster("Giant Rat", 6, 4, 0, 4, 0, [])
monlist = [m1, m2, m3, m4, m5, m6, m7, m8, m9, m10]
for mon in monlist:
    print(monlist.index(mon), mon.name)
disp1 = False
choice = input("Which monster is first combatant (enter number)? ")
try:
    choice = int(choice)
    fighter1 = monlist[choice]
    disp1 = True
    fighter1.display()
except:
    print("Sorry, that monster is not found")
choice = input("Which monster is second combatant? ")
disp2 = False
try:
    choice = int(choice)
    fighter2 = monlist[choice]
    disp2 = True
    fighter2.display()
except:
    print("Sorry, that monster is not found")
if not(disp1) or not(disp2):
    print("disp1 =" +str(disp1) + ", disp2 = "+str(disp2))
    print("Not enough combatants")
    exit
else:
    print("You have chosen "+ fighter1.name +" and "+fighter2.name)
fightcont = True
r = 1
while fightcont == True:
    print("Round", r)
    r += 1
    F1roll, f1string = fighter1.attackroll()
    F2roll, f2string = fighter2.attackroll()
    print(f1string)
    print(f2string)
    if F1roll > F2roll:
        dealt1 = fighter1.dealdamage()
        result = fighter2.damaged(dealt1)
    elif F2roll > F1roll:
        dealt2 = fighter2.dealdamage()
        result = fighter1.damaged(dealt2)
    else:
        print("That round was a draw")
    print(fighter1.name, fighter1.status)
    print(fighter2.name, fighter2.status)
    if fighter1.status == "is dead":
        fightcont = False
        winner = fighter2
        loser = fighter1
    elif fighter2.status == "is dead":
        fightcont = False
        winner = fighter1
        loser = fighter2
print ("Winner is: " + winner.name)
winner.picksmoney(loser.dropsmoney())
winner.picksitems(loser.dropsitems())
Yes it is big (as far as programs on this blog go). But it works, and here are typical results:
==== RESTART: C:\Users\pc\Documents\Programming\fightingfantasy_combat.py ====
0 Orc
1 Minotaur
2 Giant Spider
3 Werewolf
4 Dragon
5 Dwarf
6 Elf
7 Killer Weasel
8 Assassin
9 Giant Rat
Which monster is first combatant (enter number)? 1
Name    :  Minotaur
Skill   :  8
Stamina :  8
Armor   :  1
Damage  :  6
Treasure:  12
Equip   :  battleaxe
Which monster is second combatant? 8
Name    :  Assassin
Skill   :  8
Stamina :  6
Armor   :  1
Damage  :  6
Treasure:  10
Equip   :  leatherarmour, scimitar
You have chosen Minotaur and Assassin
Round 1
Minotaur rolls 1, 1 + 8 skill = 10
Assassin rolls 3, 2 + 8 skill = 13
Assassin deals 1 damage
Minotaur has 8 Stamina left
Assassin Full Health
Round 2
Minotaur rolls 4, 6 + 8 skill = 18
Assassin rolls 2, 5 + 8 skill = 15
Minotaur deals 2 damage
Minotaur has 8 Stamina left
Assassin has 5 Stamina left
Round 3
Minotaur rolls 2, 1 + 8 skill = 11
Assassin rolls 6, 5 + 8 skill = 19
Assassin deals 3 damage
Minotaur has 6 Stamina left
Assassin has 5 Stamina left
Round 4
Minotaur rolls 3, 2 + 8 skill = 13
Assassin rolls 6, 6 + 8 skill = 20
Assassin deals 3 damage
Minotaur has 4 Stamina left
Assassin has 5 Stamina left
Round 5
Minotaur rolls 5, 2 + 8 skill = 15
Assassin rolls 5, 2 + 8 skill = 15
That round was a draw
Minotaur has 4 Stamina left
Assassin has 5 Stamina left
Round 6
Minotaur rolls 3, 3 + 8 skill = 14
Assassin rolls 5, 5 + 8 skill = 18
Assassin deals 4 damage
Minotaur has 1 Stamina left
Assassin has 5 Stamina left
Round 7
Minotaur rolls 5, 1 + 8 skill = 14
Assassin rolls 6, 5 + 8 skill = 19
Assassin deals 2 damage
Minotaur is dead
Assassin has 5 Stamina left
Winner is: Assassin
Minotaur drops 12
Assassin has picked up 12
Assassin now has treasure:  22
Minotaur drops  ['battleaxe']
Assassin has picked up ['battleaxe']
Assassin equipment is leatherarmour, scimitar, battleaxe
>>>

Things to do to improve this program:

• Equipment could influence combat stats, especially for humanoid creatures that can use it (a dragon would have no use for chainmail armour but an elf might). This may involve an item class and objects. 
• Currently if the same creature is selected twice, there is just one object that fights itself (a rather bizarre situation). It would be better if a duplicate object could be created with a different name but same combat stats. 
• Currently no creatures can used ranged/missile weapons (bows & arrows, magic spells, dragon's fiery breath). Perhaps those creatures with ranged attacks get a free attack before regular hand-to-hand combat ensues. 
• As noted above, storing the data for each combatant could be done better, either in a CSV file or an SQLite database. 
• The grand project is to combine this combat system with the adventuring exploration system shown in the post about a simple adventure program to more closely replicate the original Fighting Fantasy books.  
• In that case, items may be used to deal with non-combat situations (such as using a picked-up torch to light a darkened room, thereby revealing a dangerous pit).
• It would also involve creating a Player-Character object representing the player interacting with the fantasy world.