A simple database program

So we know how to get Python to run the most basic of SQLite commands - enough to maintain a table. As I discussed earlier, one of the big benefits of Python is that it can make SQLite commands more user friendly. Even if you don't use a GUI, Python can make command line interactions easier.
Building on the database I've been using in the previous posts, I've cooked up a Python program that makes using the historical figures database easier.

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

def viewtable():
    curs.execute("SELECT * FROM historicalfigures ORDER BY Name;")
    fulldump = curs.fetchall()
    print("Name                  | Born  | Died  | Role                  | Nation")
    print("======================|=======|=======|=======================|=========")
    for line in fulldump:
        namestr = line[0]
        if len(namestr) >= 23:
            namestr = namestr[0:22]
        rolestr = line[3]
        if len(rolestr) >= 23:
            rolestr = rolestr[0:22]
        nationstr = line[4]
        if len(nationstr) >= 17:
            nationstr = nationstr[0:16]
        gapname = " " * (22 - len(namestr)) + "| "
        gapborn = "  | "
        gapdied = "  | "
        gaprole = " " * (22 - len(rolestr)) + "| "
        print(namestr + gapname + str(line[1]) + gapborn + str(line[2]) + gapdied + rolestr + gaprole + nationstr)
   
def searchtable():
    print("Searching table - please select which column ")
    print("Name ----(N)")
    print("Born ----(B)")
    print("Died ----(D)")
    print("Role ----(R)")
    print("Nation --(T)")
    fieldchoice = input("? ")
    fieldchoice = fieldchoice.lower()
    if fieldchoice == "n":
        term = input("What name? ")
        searchterm = "Name = '" + term + "'"
    elif fieldchoice == "b":
        compare = input("Do you want results before ( < ), in exact year ( = ) or after ( > )? ")
        term = input("Which Year? ")
        searchterm = "YearBirth "+ compare + " " + term
    elif fieldchoice == "d":
        field = "YearDeath"
        compare = input("Do you want results before ( < ), in exact year ( = ) or after ( > )? ")
        term = input("Which Year? ")   
        searchterm = "YearDeath "+ compare + " " + term
    elif fieldchoice == "r":
        term = input("What Role? ")
        searchterm = "Role LIKE '%"+ term +"%'"
    elif fieldchoice == "t":
        term = input("What Nation? ")
        searchterm = "Nation LIKE '%" + term + "%'"
    else:
        print("Sorry, option not recognised. ")
        return()
    print (searchterm)
    curs.execute("SELECT * FROM historicalfigures WHERE " + searchterm +";")
    searchdump = curs.fetchall()
    print("Name                   | Born  | Died  | Role                  | Nation")
    print("=======================|=======|=======|=======================|=========")
    for line in searchdump:
        gapname = " " * (23 - len(line[0])) + "|"
        gapborn = " " * 3 + "|"
        gapdied = " " * 3 + "|"
        gaprole = " " * (23 - len(line[3])) + "|"
        print(line[0] + gapname + str(line[1]) + gapborn + str(line[2]) + gapdied + line[3] + gaprole + line[4])
   
def enternew():
    print("Entering new record")
    newname = input("Please enter new name: ")
    newbirth = input("Please enter new year of birth: ")
    newdeath = input("Please enter new year of death: ")
    newrole = input("Please enter new role: ")
    newnation = input("Please enter new nation: ")
    keeper = input("Do you want to keep this new record (Y to keep, N to discard): ")
    keeper = keeper.lower()
    if keeper == "y":
        curs.execute("INSERT INTO historicalfigures (Name, YearBirth, YearDeath, Role, Nation) VALUES ('" + newname + "', "+ newbirth +", "+ newdeath +", '" + newrole + "', '" +newnation +"');")
        curs.execute("COMMIT;")
   
def deleterecord():
    curs.execute("SELECT Name FROM historicalfigures ORDER BY Name;")
    namedump = curs.fetchall()
    print("Current list of names is:")
    linecount = 0
    for line in namedump:
        print(linecount + " - " + line[0])
        linecount += 1
    whichline = input("Enter number of row to be deleted: ")
    whichline = int(whichline)
    delname = namedump[whichline][0]
    confirm = input("Please confirm (Y) or cancel (N) deleting record for " + delname +": ")
    confirm = confirm.lower()
    if confirm == 'y':
        curs.execute("DELETE FROM historicalfigures WHERE Name ='" + delname + "';")
        curs.execute("COMMIT;")
   
def updaterecord():
    curs.execute("SELECT Name FROM historicalfigures ORDER BY Name;")
    namedump = curs.fetchall()
    print("Current list of names is:")
    linecount = 0
    for line in namedump:
        print(str(linecount) + " - " + line[0])
        linecount += 1
    whichline = input("Enter number of row to be changed: ")
    whichline = int(whichline)
    updatename = namedump[whichline][0]
    curs.execute("SELECT * FROM historicalfigures WHERE Name = '"+ updatename+"';") 
    changedump = curs.fetchall()
    coltuple = ('Name', 'YearBirth', 'YearDeath', 'Role', 'Nation')
    colcount = 0
    for col in changedump[0]:
        print (str(colcount), coltuple[colcount], changedump[0][colcount])
        colcount += 1
    colchoice = input("Please enter number of column to change: ")
    colchoice = int(colchoice)
    newvalue = input("Please enter new value for this column: ")
    if colchoice == 0 or colchoice == 3 or colchoice == 4:
        newvalue = "'"+ newvalue +"'"
    print("About to change "+ coltuple[colchoice] +" to " + newvalue + " for "+ updatename)
    confirmchange = input("Confirm(Y) or discard (N): ")
    confirmchange = confirmchange.lower()
    if confirmchange == 'y':
        curs.execute("UPDATE historicalfigures SET "+ coltuple[colchoice] +" = "+ newvalue +" WHERE Name = '" + updatename + "';")
        curs.execute("COMMIT;")
print ("Historical Figures Table")
print ("========================")
cont = True
while cont == True:
    print ("Main menu - Please select an option")
    print ("View whole table ---V")
    print ("Search table -------S")
    print ("Enter new record ---E")
    print ("Delete record ------D")
    print ("Update record ------U")
    print ("Quit Program -------Q")
    optchoice = input("? ")
    optchoice = optchoice.lower()
    if optchoice == 'v':
        viewtable()
    elif optchoice == 's':
        searchtable()
    elif optchoice == 'e':
        enternew()
    elif optchoice == 'd':
        deleterecord()
    elif optchoice == 'u':
        updaterecord()
    elif optchoice == 'q':
        conn.close()
        cont = False
    else:
        print("Sorry, not recognised")


So this is big - bigger than any other program I've created for this blog. And what are the results like?

========= RESTART: C:\Users\pc\Documents\Programming\historical_2.py =========
Historical Figures Table
========================
Main menu - Please select an option
View whole table ---V
Search table -------S
Enter new record ---E
Delete record ------D
Update record ------U
Quit Program -------Q
? v
Name                  | Born  | Died  | Role                  | Nation
======================|=======|=======|=======================|=========
Cardinal Thomas Wolsey| 1473  | 1530  | Churchman & Politician | England
Christopher Columbus  | 1451  | 1509  | Explorer               | Spain & Italy
Elizabeth I           | 1533  | 1603  | Monarch                | England
Ferdinand Magellan    | 1480  | 1521  | Explorer               | Portugal
Francis Drake         | 1540  | 1596  | Explorer & Naval Comma | England
Henry VIII            | 1491  | 1547  | Monarch                | England
Leonardo da Vinci     | 1452  | 1519  | Artist                 | Italy
Niccolo Machiavelli   | 1469  | 1527  | Politician & Author    | Italy (Florence)
William Caxton        | 1422  | 1491  | Publisher              | England
William Shakespeare   | 1564  | 1616  | Author & Playwright    | England
Main menu - Please select an option
View whole table ---V
Search table -------S
Enter new record ---E
Delete record ------D
Update record ------U
Quit Program -------Q
?
========= RESTART: C:\Users\pc\Documents\Programming\historical_2.py =========
Historical Figures Table
========================
Main menu - Please select an option
View whole table ---V
Search table -------S
Enter new record ---E
Delete record ------D
Update record ------U
Quit Program -------Q
? v
Name                  | Born  | Died  | Role                  | Nation
======================|=======|=======|=======================|=========
Cardinal Thomas Wolsey| 1473  | 1530  | Churchman & Politician| England
Christopher Columbus  | 1451  | 1509  | Explorer              | Spain & Italy
Elizabeth I           | 1533  | 1603  | Monarch               | England
Ferdinand Magellan    | 1480  | 1521  | Explorer              | Portugal
Francis Drake         | 1540  | 1596  | Explorer & Naval Comma| England
Henry VIII            | 1491  | 1547  | Monarch               | England
Leonardo da Vinci     | 1452  | 1519  | Artist                | Italy
Niccolo Machiavelli   | 1469  | 1527  | Politician & Author   | Italy (Florence)
William Caxton        | 1422  | 1491  | Publisher             | England
William Shakespeare   | 1564  | 1616  | Author & Playwright   | England
Main menu - Please select an option
View whole table ---V
Search table -------S
Enter new record ---E
Delete record ------D
Update record ------U
Quit Program -------Q
? e
Entering new record
Please enter new name: Dante Alighieri
Please enter new year of birth: 1265
Please enter new year of death: 1321
Please enter new role: Author & Poet
Please enter new nation: Italy (Florence)
Do you want to keep this new record (Y to keep, N to discard): y
Main menu - Please select an option
View whole table ---V
Search table -------S
Enter new record ---E
Delete record ------D
Update record ------U
Quit Program -------Q
? y
Sorry, not recognised
Main menu - Please select an option
View whole table ---V
Search table -------S
Enter new record ---E
Delete record ------D
Update record ------U
Quit Program -------Q
? e
Entering new record
Please enter new name: Michelangelo di Lodovico Buonarotti Simoni
Please enter new year of birth: 1475
Please enter new year of death: 1564
Please enter new role: Artist
Please enter new nation: Italy (Florence & Rome)
Do you want to keep this new record (Y to keep, N to discard): y
Main menu - Please select an option
View whole table ---V
Search table -------S
Enter new record ---E
Delete record ------D
Update record ------U
Quit Program -------Q
? q
>>>

There are plenty of points I would like to make about this program:

• It relies on functions to split the program into manageable chunks. Each choice (except Q to quit) in the main menu calls on a user-defined function defined before the main program.
• I didn't need to reopen the connection to the database or recreate the cursor object when in the user-defined functions - they can automatically use the ones from the main program. 
• The function viewtable()is about taking the list of tuples returned from SQLite by curs.fetchall() and presenting it neatly on the IDLE command line. One unforeseen problem is this blog has a narrower column width than the IDLE interface, so I've shrunk the font on the output to get the table to look neat. 
• In viewtable() I have used slicing strings to make oversized text strings fit into columns, and repeating strings with * to fill with the right number of spaces. I'm hoping that all dates just have 4 digits. 
• In searchtable() I have used user choices to assemble an SQL command. For searching both role and nation I have used the LIKE SQL comparator. This looks for records in a less exacting way than =  , and as here will find records where the searched-for string is part of that record's data in that field. So when searching the Role column, LIKE allows me to find author in a record whose Role field is author & playwright.  
• All functions that involve changing the database have a confirming part, where if the user confirms they want to make the change, the COMMIT SQLite command is used. 
• Both  deleterecord() and updaterecord() present a list of names (from the first column in the database) and allows the user to select the name by its index (its numerical position in the list). This name is then used as the identifier to make sure the right record in the database is updated or deleted as appropriate.  

DELETE to Remove Records and UPDATE to modify records in SQLite and Python

There are two main reasons for wanting to edit the data in a database - either the thing the database represents has changed, or the data was entered incorrectly in the first place. Either way, modifying and removing existing records is an important part of maintaining a database. In SQLite these are achieved by UPDATE and DELETE respectively.

Let's go back to our single-table database of historical people.

>>> import sqlite3
>>> conn = sqlite3.connect("C:\\sqlite\\db\\history1.db")
>>> curs = conn.cursor()
>>> curs.execute("SELECT * FROM historicalfigures;")
<sqlite3.Cursor object at 0x039DD360>
>>> dumpa = curs.fetchall()
>>> for a in dumpa:
 print(a)

('Leonardo da Vinci', 1452, 1519, 'Artist', 'Italy')
('Niccolo Machiavelli', 1469, 1527, 'Politician & Author', 'Italy (Florence)')
('Cardinal Thomas Wolsey', 1473, 1530, 'Churchman & Politician', 'England')
('William Caxton', 1422, 1491, 'Publisher', 'England')
('William Shakespeare', 1564, 1616, 'Author & Playwright', 'England')
('Francis Drake', 1540, 1596, 'Explorer', 'England')
('Henry VIII', 1491, 1547, 'Monarch', 'England')
('Elizabeth I', 1533, 1603, 'Monarch', 'England')
('Christopher Columbus', 1451, 1509, 'Explorer', 'Spain & Italy')
('Ferdinand Magellan', 1480, 1521, 'Explorer', 'Portugal')
>>> 

So far so good. But I want to modify a datum - I realise that Francis Drake was a naval commander as well as an explorer (he led numerous naval attacks against the Spanish and helped defeat the Spanish Armarda). The general format for UPDATE is
UPDATE tablename SET update_list WHERE predicate;

That is just SQLite. To get Python to pass it to SQLite we use the cursor object like this:
>>> curs.execute("UPDATE historicalfigures SET Role = 'Naval Commander & Explorer' WHERE Name = 'Francis Drake';")

As before, I have used single quotes ('') within the SQLite command, and double quotes ("")for the Python command, with the results:

<sqlite3.Cursor object at 0x039DD360>

>>> curs.execute("SELECT * FROM historicalfigures WHERE Name = 'Francis Drake';")
<sqlite3.Cursor object at 0x039DD360>
>>> curs.fetchall()
[('Francis Drake', 1540, 1596, 'Naval Commander & Explorer', 'England')]
>>>

I have found to my annoyance that it is very important to include the WHERE part of the statement to specify which entry (or entries) should be changed. I have made the stupid mistake of leaving that part out , then finding I have changed every record in the table - if you don't specify which records in the table should be changed by a command, SQLite assumes you mean all of them.
This is of vital importance when dealing with the next SQLite command - DELETE. This follows the format:
DELETE FROM tablename WHERE predicate;

Let's say we want to remove William Caxton from the table - we don't think he's historically significant enough (though he brought the first printing press to England). Again it is vital that we are careful about which records we delete.

>>> curs.execute("DELETE FROM historicalfigures WHERE Name = 'William Caxton';")
<sqlite3.Cursor object at 0x039DD360>
>>> curs.execute("SELECT * FROM historicalfigures;")
<sqlite3.Cursor object at 0x039DD360>
>>> dumpb = curs.fetchall()
>>> for b in dumpb:
 print(b)

('Leonardo da Vinci', 1452, 1519, 'Artist', 'Italy')
('Niccolo Machiavelli', 1469, 1527, 'Politician & Author', 'Italy (Florence)')
('Cardinal Thomas Wolsey', 1473, 1530, 'Churchman & Politician', 'England')
('William Shakespeare', 1564, 1616, 'Author & Playwright', 'England')
('Francis Drake', 1540, 1596, 'Naval Commander & Explorer', 'England')
('Henry VIII', 1491, 1547, 'Monarch', 'England')
('Elizabeth I', 1533, 1603, 'Monarch', 'England')
('Christopher Columbus', 1451, 1509, 'Explorer', 'Spain & Italy')
('Ferdinand Magellan', 1480, 1521, 'Explorer', 'Portugal')
>>> 

And we can see that William Caxton has been removed (and only that record...).

SELECT in SQLite and Using it in Python

SELECT is the main command that retrieves data from SQLite databases. We have encountered it before when I connected to an SQLite database. I then used SELECT to collect data from both the master table about what tables were within the database:
>>curs.execute("SELECT * FROM sqlite_master WHERE type = 'table';")
and also from another table:
>>> curs.execute("SELECT * FROM Location;")

As you can see, the SELECT commands are both part of execute commands, sent from Python via the curs object to SQLite.
In both commands, the general format is:
SELECT column_names FROM table_name (conditions/predicate);

and as we have seen, you then need to use curs.fetchall() to retrieve the data from SQLite back to Python.
The * in both commands where column names should be tells SQLite to return data from all columns in the table. If we go back to our database of historical figures:
>>> import sqlite3
>>> conn = sqlite3.connect("C:\\sqlite\\db\\history1.db")
>>> curs = conn.cursor()
>>> curs.execute("SELECT * FROM historicalfigures;")
<sqlite3.Cursor object at 0x0369B620>
>>> fulldump = curs.fetchall()
>>> for line in fulldump:
 print(line)

('Leonardo da Vinci', 1452, 1519, 'Artist', 'Italy')
('Niccolo Machiavelli', 1469, 1527, 'Politician & Author', 'Italy (Florence)')
('Cardinal Thomas Wolsey', 1473, 1530, 'Churchman & Politician', 'England')
('William Caxton', 1422, 1491, 'Publisher', 'England')
('William Shakespeare', 1564, 1616, 'Author & Playwright', 'England')
('Francis Drake', 1540, 1596, 'Explorer', 'England')
('Henry VIII', 1491, 1547, 'Monarch', 'England')
('Elizabeth I', 1533, 1603, 'Monarch', 'England')
>>>
As you can see, I've added a few more records to the table.
If I want only a few columns I can adjust the SELECT command accordingly:
>>> curs.execute("SELECT name, nation FROM historicalfigures;")
<sqlite3.Cursor object at 0x0369B620>
>>> partialdump = curs.fetchall()
>>> for line in partialdump:
 print(line)

('Leonardo da Vinci', 'Italy')
('Niccolo Machiavelli', 'Italy (Florence)')
('Cardinal Thomas Wolsey', 'England')
('William Caxton', 'England')
('William Shakespeare', 'England')
('Francis Drake', 'England')
('Henry VIII', 'England')
('Elizabeth I', 'England')
>>>

What if you are not sure about what columns there are in a table, or you aren't sure of their exact names? I found a convenient SQLite command called PRAGMA.
>>> curs.execute("PRAGMA table_info('historicalfigures');")
<sqlite3.Cursor object at 0x0369B620>
>>> pragdump = curs.fetchall()
>>> for pragline in pragdump:
 print(pragline)

(0, 'Name', 'text', 0, None, 1)
(1, 'YearBirth', 'integer', 0, None, 0)
(2, 'YearDeath', 'integer', 0, None, 0)
(3, 'Role', 'text', 0, None, 0)
(4, 'Nation', 'text', 0, None, 0)
>>> 
I'm not entirely sure what the last two columns in this pragma table are, but it is clear the first one is the order of the columns, the second is the name of each column and the third is the data type.

So what if you've got a table of data and you want to find all the records that match a simple criterion - for example, all the historical figures who were monarchs. This is where the SQL conditional WHERE comes in use. I think of it a bit like an if structure in Python - SQLite checks if the WHERE condition is true for each row/record, and if so it is included in the results.

>>> curs.execute("SELECT * FROM historicalfigures WHERE Role = 'Monarch';")
<sqlite3.Cursor object at 0x0369B620>
>>> filterdump = curs.fetchall()
>>> for line in filterdump:
 print(line)

('Henry VIII', 1491, 1547, 'Monarch', 'England')
('Elizabeth I', 1533, 1603, 'Monarch', 'England')
>>>
For columns that are numbers (integers or real/floats), you can also use > (greater than) or < (less than) with WHERE to select records greater or less than a particular number. For example, which people in the table were born before 1500?

>>> curs.execute("SELECT Name, YearBirth FROM historicalfigures WHERE YearBirth < 1500;")
<sqlite3.Cursor object at 0x0369B620>
>>> filterdump = curs.fetchall()
>>> for line in filterdump:
 print(line)

('Leonardo da Vinci', 1452)
('Niccolo Machiavelli', 1469)
('Cardinal Thomas Wolsey', 1473)
('William Caxton', 1422)
('Henry VIII', 1491)
>>>
I have only scratched the surface of what SELECT can do, but it is enough for us to get started. I may come back to SELECT and expand on its capabilities later.

INSERT in SQLite and Using it in Python

Previously I looked at creating an SQLite database and  creating an empty table within it. The simplest way of getting data into a SQLite database table is to use the INSERT SQL command, and the simple version follows the format:
INSERT INTO tablename (column_list) VALUES (value_list);

Although not strictly necessary, I keep SQLite command and function names and keywords in UPPERCASE to remind me what they are. The other items in that command are variable. So if we go back to the table for historical figures we created, we might have an example of the INSERT function like:
INSERT INTO historicalfigures (Name, YearBirth, YearDeath, Role, Nation) VALUES ('Henry VIII', 1491, 1547, 'King', 'England');
That is just the SQL part. To get Python to do this, we need to do it via the cursor object (here given the variable name curs).
curs.execute("INSERT INTO historicalfigures (Name, YearBirth, YearDeath, Role, Nation) VALUES ('Henry VIII', 1491, 1547, 'King', 'England');")

That is longwinded, and not very clear. Do we need to do that every time we want to add a new row?
Yes and no.
Yes we need to get Python to tell SQL to do this, but no, we don't have to do this manually. This is where other parts of Python programming come in use in automating and easing this process.
Here is a script I have written to assist with this process:

import sqlite3
conn = sqlite3.connect("C:\\sqlite\\db\\history1.db")
curs = conn.cursor()
print("Historical Figure Data Entry")
nextentry = True
committed = False
while nextentry == True:
    nameentry = input("Name of person? ")
    birthentry = input("Year of Birth? ")
    if int(birthentry) < 2020:
        print("Valid Year")
    else:
        print("problem with year of birth")
    deathentry = input("Year of Death? ")
    if int(deathentry) < 2020:
        print("Valid Year")
    else:
        print("problem with year of death")
    roleentry = input("Role of person in history? ")
    nationentry = input("Main nation for person? ")
    print("Your data is: ", nameentry, birthentry, deathentry, roleentry, nationentry)
    keepit = input("Keep (Y) or Discard (N) ?")
    keepit = keepit.lower()
    if keepit == "y":
        committed = True
        curs.execute("INSERT INTO historicalfigures (Name, YearBirth, YearDeath, Role, Nation) VALUES ('"+nameentry+"', "+birthentry+", "+deathentry+", '"+roleentry+"', '"+nationentry+"');")
    nextchoice = input("Do you want to carry on with the next entry in the table? (Y/N)")
    nextchoice = nextchoice.lower()
    if nextchoice == 'y':
        nextentry = True
    else:
        nextentry = False
   
if committed == True:
    curs.execute("COMMIT;")
conn.close()
That's quite a bit, but there are aspects I want to point out to explain what is going on:
The bulk of the program is in a while loop so that it will repeat as long as the user wants to continue.
There are a lot of input statements to gather information from the user. Some of these change the flow of the program (by if or while structures) while others are the data fields that will be sent to the database.
This is shown in the really big line
        curs.execute("INSERT INTO historicalfigures (Name, YearBirth, YearDeath, Role, Nation) VALUES ('"+nameentry+"', "+birthentry+", "+deathentry+", '"+roleentry+"', '"+nationentry+"');")
that includes these variables in the executable statement.
The mix of single quotes and double quotes is to get round the problem that strings/text data in SQLite commands need to have quotes around them but Python will also be looking for the start and end of what it considers a string. As far as Python is concerned, a single quote inside a pair of double quotes is just a string character, not a delimiter (and this works vice versa).
At the end (second from last line) there is curs.execute("COMMIT;") - this is because changes to the database by INSERT and other commands are held in a temporary state by SQLite. If changes are made but then the COMMIT command is not used, the changes are discarded. This COMMIT command tells SQLite to keep the data changes.
However, if there are no changes, and then the COMMIT command is used, SQLite throws an error because there are no changes to be committed - hence the committed variable to tell the program if the COMMIT command should be sent to SQLite.

The end result? A more user-friendly way of entering data into our table:

=== RESTART: C:/Users/pc/Documents/Programming/historicalfigures_entry.py ===
Historical Figure Data Entry
Name of person? Leonardo da Vinci
Year of Birth? 1452
Valid Year
Year of Death? 1519
Valid Year
Role of person in history? Artist
Main nation for person? Italy
Your data is:  Leonardo da Vinci 1452 1519 Artist Italy
Keep (Y) or Discard (N) ?y
Do you want to carry on with the next entry in the table? (Y/N)n
>>>

Creating SQLite Databases and Tables in Python

Creating a new database is so easy you could do it almost by accident. In the previous post I described how you connect to an existing database with a few lines of code:
import sqlite
conn = sqlite.connect("C:\\sqlite\\db\\base1.db")

Well, you do exactly the same for a new database, just use a filename that doesn't exist yet. And if you try to connect to an existing database but misspell the filename, you might do just that. When I've made that sort of mistake, my first clue is that the tables I expect to be there aren't. The first time this led to a panic about the whole database being deleted, when in fact I had just created and was looking into a new empty database file. The intended database file was still there, unconnected.

Let's assume that we intentionally create a new database file. SQLite databases hold their data in tables. I don't feel this is the place to go into the details of deciding how your data should be structured - I shall leave it to the reader to decide what the tables and columns of each table should be. But let's say we do the following:

>>> conn = sqlite3.connect("C:\\sqlite\\db\\example.db")

Then, as mentioned in the previous post, we create a cursor object so that we can send commands to and receive responses from SQLite. 

>>> curs = conn.cursor()

Let's say, for example, we want a database of famous historical figures. We start off with one table, with the following columns/fields with data types:

• Name (text) - Primary Key (i.e. the unique identifier for each record)
• Year of Birth (integer)
• Year of Death (integer)
• Role (text)
• Nation (text)

Note that SQLite has data types similar to Python data types, though slightly different. SQLite has integers (int() in Python), text (strings or str() in Python) and real (floating point numbers - float() in Python). There are other types but I haven't got round to them yet.  

How do we create this table? The SQLite command is in the following format: 

CREATE table tablename (column1 column1_type, column2 column2_type, ... );

So for our database of historical figures, we could create a table using something like:

CREATE table historicalfigures (Name text primary key, YearBirth integer, YearDeath integer, Role text, Nation text);

However, that is purely SQL. To get this to happen using Python, we need to get Python to pass it to SQLite using the cursor object curs: 

curs.execute("CREATE table historicalfigures (Name text primary key, YearBirth integer, YearDeath integer, Role text, Nation text);")

There is no immediate response, unless there is something wrong, in which case you will get an error message. Assuming there are no error messages, we have just used Python to create a SQLite database and an empty table.  

  

And finally, we close the connection
conn.close()

Connecting to SQLite and Cursor Objects

SQLite, as mentioned in previous posts, is a software package for managing databases. It does so mainly through SQL, Standard Query Language. There are other database software packages that use SQL, namely MySQL (I didn't like it but you may have better luck), PostgreSQL and Oracle. SQLite has the nice feature that it integrates with programming languages such as Python, C++, Perl, Java and JavaScript.

So how do you use it? First you need to install it (my experience described here). Then you write a Python program that imports the sqlite3 module, connects to the database, and creates a virtual cursor. For example, if my SQLite is installed at C:\\sqlite and the database is at C:\\sqlite\db\base1.db then I create a connection by

import sqlite3 # only needed once at the start
conn = sqlite3.connect("C:\\SQLite\db\base1.db")

Remember when I looked at files, and I talked about creating a file object? This is basically what the conn variable represents. I think of it as a specialised file object, and the sqlite3 module allows us to interact in more varied ways with this file than just the usual read, write or append. 

Once you have made the connection with your chosen database you create what is called a cursor object. This is a virtual cursor - you don't see it flashing on your screen, but it enables you to pass SQL commands from Python to the SQLite program, and retrieve answers. 

So if we call our virtual cursor object curs (just a variable, but one I can recognise), we create it by
curs = conn.cursor()

Now we can tell Python to pass SQL commands to the SQLite program which will interact with the database. 
The bad news is SQL is a language in itself (that's what the L stands for). So the SQLite module lets you use a language within another language. 
The good news is SQL is not particularly difficult. 

One quick way of finding out the tables in a database is by querying the SQLite_master table. So the python command to run the SQL command is

curs.execute("SELECT * FROM sqlite_master WHERE type = 'table';")

Everything within the double quotes is being passed by the cursor object (curs) to the SQLite3 program. Also note the semi-colon at the end of each SQL command. This command on its own does not produce much on screen. If you are doing this on the IDLE command line rather than in a script, you might get a reply like
<sqlite3.Cursor object at 0x038DD360>
Not very informative. It's actually saying that a memory location has something from the cursor object. If execute() sends instructions from Python to SQL, then fetchall() and fetchone() bring back the reply.
I find it best to assign these replies to variables within Python. So
dump = curs.fetchall()
will retrieve whatever reply the curs object has received from SQL and pass it to Python, holding it in the variable dump. So what does dump hold?
>>> print(dump)
[('table', 'Location', 'Location', 2, 'CREATE TABLE Location(Scientific_name TEXT, Nation TEXT, Biome TEXT)')]
>>>
This might look like a mess, but a lot of queries will return in this format, and Python understands what this is. It is a list (denoted by the square brackets) that contains a tuple (denoted by the outer set of round brackets). The list is all the lines returned, with each line returned as a tuple - here it is just one line, so just one tuple within the list. Within the tuple, each element is the contents of the column (or field) for that row. In spreadsheet terms each element in the tuple would be a cell.

I admit this example is helped by the fact that I have already done some work on base1.db, so there is something to show for the queries.  It's like one of those "how to..." programmes on the TV where rather than going through the whole process, the presenter moves to a different workbench and announces "And here's one I did earlier" much to the frustration of the audience trying to follow.
So what tables are in this database? Just one, called Location (hence just one tuple in the response), and the final element in the tuple is actually the  SQL command used to create the table (here 'CREATE TABLE Location(Scientific_name TEXT, Nation TEXT, Biome TEXT)'. I won't go into a detailed explanation here.
So what if I then run the same two commands (execute() and fetchall()) for this table Location?
>>> curs.execute("SELECT * FROM Location;")
<sqlite3.Cursor object at 0x038DD360>
>>> dump2 = curs.fetchall()
>>> print(str(dump2))
[('Homo sapiens', 'United Kingdom', 'Urban'), ('Homo sapiens', 'United Kingdom', 'Rural'), ('Rattus norvegicus', 'United Kingdom', 'Urban'), ('Rattus norvegicus', 'United Kingdom', 'Rural'), ('Carcharodon carcharias', 'South Africa', 'Ocean'), ('Homo sapiens', 'South Africa', 'Urban'), ('Homo sapiens', 'South Africa', 'Ocean'), ('Carcharodon carcharias', 'Australia', 'Ocean'), ('Homo sapiens', 'Australia', 'Ocean')]

That's a mess (hence my choice of variable name).
But if I then tell Python to loop through the list (leaving the tuples as tuples) we get:
>>> for line in dump2:
 print(line)

('Homo sapiens', 'United Kingdom', 'Urban')
('Homo sapiens', 'United Kingdom', 'Rural')
('Rattus norvegicus', 'United Kingdom', 'Urban')
('Rattus norvegicus', 'United Kingdom', 'Rural')
('Carcharodon carcharias', 'South Africa', 'Ocean')
('Homo sapiens', 'South Africa', 'Urban')
('Homo sapiens', 'South Africa', 'Ocean')
('Carcharodon carcharias', 'Australia', 'Ocean')
('Homo sapiens', 'Australia', 'Ocean')
>>>
which is more clearly a table of results, with each tuple containing three elements (here Scientific Name, Nation and Biome respectively - biome is a biological term for what sort of habitat). This table actually was part of an early draft of the project I have been working on.

Finally, when finishing with SQLite, it is sensible to close any connections (again similar to closing a file object). This also closes the cursor object. So:
>>> conn.close()
>>> 
More later...

Reviving this Blog after a very long break...

Until a few days ago , my time has been taken up with Open University studying and coursework. Python programming for its own sake has been put on hold, and any programming has been to further my OU course.
I have recently handed in what I believe to be my last piece of coursework. Should I pass this module I will graduate from the OU with a degree in IT and Computing and start looking for a job.
This is all good news. Right now I have nothing urgent to do, so restarting this blog seems like a good idea.
My final project on the course actually involved Python programming, and SQLite. It is a biodiversity database (at least the framework for one - there isn't a great deal of actual data), and my previous post (way back in November last year) guided my choice of software. I wanted to do something similar to a LAMP architecture - Linux (as operating system), Apache (as web server software) , MySQL (as the database) and PHP (for scripts and programs).
What I ended up doing was WASP - like LAMP but each item substituted for an equivalent. Windows (operating system), Abyss (web server) SQLite (for the database) and Python (for the scripts).
Although focussing on only a few aspects of Python, the project did teach me a lot.
SQLite - in my previous post I installed SQLite without really knowing how to use it. This project required me to learn about SQLite - how to set up tables and how to add and change data in those tables. Just as importantly I learned how to use the sqlite3 module to get Python programs to do these things.
Big programs - maybe not full-on commercial application size, but some of the scripts I was writing ended up taking up about 200 lines, or 2-3 sheets of A4 paper, making them the largest I've written. That's when user-defined functions, well-named variables and readable code really helps.
CGI - I had to learn this from scratch. Basically this module allows Python scripts to accept input from web forms across the web. So my project had web pages that users on the other side of the world could see if they know the domain name (http://biodiversity.ddns.net/) thanks to the webserver. These web pages have web forms that allow users on their web browsers to submit information to a python script on my web-serving computer. The CGI module enabled the script to receive this information and act on it.
Creating dynamic web pages - This is usually the domain of PHP (another programming language, favoured among web developers) but it can be done with Python. Dynamic web pages are changeable, depending on inputs and conditions, unlike static web pages that simply produce the same content again and again. The Python scripts use the sqlite3 module to read from the SQLite database and then create web pages using the data from the database.

Anyway, it's good to be back here again.