Changes to the Database of Historical Figures

I have gone into SQLite and done the following:

Microsoft Windows [Version 10.0.17134.285]
(c) 2018 Microsoft Corporation. All rights reserved.

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

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

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

sqlite> .tables
historicalfigures   roles   weblinks

sqlite> .quit

c:\sqlite>

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

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

import sqlite3

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

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

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

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

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

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

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

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

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

import sqlite3
conn = sqlite3.connect("C:\\sqlite\\db\\history1.db")
curs = conn.cursor()
#List of User-defined Functions
#addlink()
#addrole()
#checkint()
#deleterecord()
#deleterole()
#deletelink()
#displayrecord()
#displaytable()
#enternew()
#searchtable()
#updaterecord()
#viewtable()

def addlink():
    ….
def addrole():
    ….
def checkint(num):
    ….
def deleterecord():
    ….
def deletelink():
    ….
def deleterole():
    ….  
def displayrecord(name):
    ….
def displaytable(dump):
    ….
def enternew():
    ….
def searchtable():
    ….
def updaterecord():
    ….
def viewtable():
    ….

#Main program
print ("Historical Figures Table")
print ("========================")
cont = True
while cont == True:
    print ("Main menu - Please select an option")
    print ("View whole table ---V")
    print ("Search table -------S")
    print ("Enter new record ---E")
    print ("Delete record ------D")
    print ("Update record ------U")
    print ("Quit Program -------Q")
    print ("Add/Delete Role ----R")
    print ("Add/Delete Weblink -W")
    optchoice = input("? ")
    optchoice = optchoice.lower()
    if optchoice == 'v':
        viewtable()
    elif optchoice == 's':
        searchtable()
    elif optchoice == 'e':
        enternew()
    elif optchoice == 'd':
        deleterecord()
    elif optchoice == 'u':
        updaterecord()
    elif optchoice == 'q':
        conn.close()
        cont = False
    elif optchoice == 'r':
        rolech = input ("Add (A) or Delete (D)? ")
        rolech = rolech.lower()
        if rolech == 'd':
            deleterole()
        elif rolech == 'a':
            addrole()
    elif optchoice == 'w':
        linkch = input("Add (A) or Delete (D)? ")
        linkch = linkch.lower()
        if linkch == 'd':
            deletelink()
        elif linkch == 'a':
            addlink()
    else:
        print("Sorry, not recognised")

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