CSc 250: Lecture Notes: Implementing roles in sqlite3

We previously implemented getrole.py and addrole.py is the file io lecture notes. In those implementations, we used a text file to store the role information. In this lecture, we will implement the same program, but we will use a sqlite3 database to store the relationships. Review the linked notes if you forgot.

Creating the DB

If you recall, the old implementation of addrole.py used a file formatted as follows to get role/actor info:

Han Solo       | Harrison Ford
Luke Skywalker | Mark Hamill
Batman         | Christian Bale
Thor           | Chris Hemsworth
Sherlock       | Benedict Cumberbatch

Instead, we will create a database to store this information. Before using sqlite3 to accomplish this, we need to create an empty database with the correct schema.

The first step is to open a bash shell and cd into the directory that you want the database file to be stored at. One you are there run sqlite3, and create a table to store the mapping between roles and actors:

$ cd
$ cd test/
$ sqlite3 roledb
SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
sqlite> CREATE TABLE role (character_name TEXT, actor_name TEXT);
sqlite> .exit
$

Only one table is needed to model this relationship, so this is all we need.

At this point, we should have a sqlite database file named roledb in this directory. This database has the schema specified, but not data in it.

Next, let’s write a program that can add roles to this database.

Adding a role

The first step is to import sqlite3 and open a connection to the database:

import sqlite3
conn = sqlite3.connect('roledb')

Next, we need to read in what roles need to be added to the database from the user. This is similar to what we did in the last addrole.py implementation. However, instead of writing the actor/character information to the text file, we must issue an INSERT command to the sqlite database to add the entry. Also, when the exit command is written by a user, we must make sure to commit and close the database connection. Putting it all together:

import sys
import sqlite3

conn = sqlite3.connect('roledb')

# Let the user add role/actor information to roles.txt until
# the exit command is given
while True:
    print ('PROGRAM: Add a role?')
    line = sys.stdin.readline().rstrip()
    sp = line.split('played')
    if line == 'exit':
        conn.commit()
        conn.close()
        sys.exit()
        print('PROGRAM: Bye!')
    elif len(sp) > 1:
        actor = sp[0].strip()
        character = sp[1].strip()
        conn.execute("INSERT INTO role VALUES('" + character + "', '" + actor + "')")
        print('PROGRAM: Role added!')
    else:
        print('PROGRAM: Huh?')

This code can be saved into a file named addrole.py. We can use this program to add a few entries to the roledb by running it from bash (or from IDLE):

$ python3 addrole.py 
PROGRAM: Add a role?
christian bale played batman
PROGRAM: Role added!
PROGRAM: Add a role?
chris hemsworth played thor
PROGRAM: Role added!
PROGRAM: Add a role?
henry cavill played superman
PROGRAM: Role added!
PROGRAM: Add a role?
exit
$

Getting Role Info

Now, we’ll implement getrole.py - a program which will return role info by querying the SQL database. Again, the first step is to import sqlite3 and open a connection to the database:

import sqlite3
conn = sqlite3.connect('roledb')

Next, we can execute a SELECT query to return all of the roles it knows about:

# SELECT all of the entries in the role table
result = conn.execute("SELECT * FROM role")

All of the query results are stored into the variable results. From here, we can put all of the results into a dictionary, like we did when we previously read the info from a file:

# Create an empty map data structure representing the database of roles
# All of the movie roles will be stored here
roles = {}

# Populate the roles dictionary using the results of the SELECT query
for entry in result:
    character = entry[0]
    actor     = entry[1]
    roles[character] = actor

Once roles is populated with all role information, the rest of the program can work basically the same way it did before. Putting it all together:

import sys
import sqlite3

# Connect to the sqlite database
import sqlite3
conn = sqlite3.connect('roledb')

# SELECT all of the entries in the role table
result = conn.execute("SELECT * FROM role")

# Create an empty map data structure representing the database of roles
# All of the movie roles will be stored here
roles = {}

# Populate the roles dictionary using the results of the SELECT query
for entry in result:
    character = entry[0]
    actor     = entry[1]
    roles[character] = actor

# Let the user get role/actor information in a loop
while True:
    print ('PROGRAM: What role would you like to know about?')
    line = sys.stdin.readline().rstrip()
    sp = line.split('tell me who played')
    
    if line == 'exit':
        print('PROGRAM: Bye!')
        sys.exit()
    elif len(sp) > 1:
        role = sp[1].strip()
        if role in roles:
            print(roles[role] + ' played ' + role)
        else:
            print('PROGRAM: Not sure!')
    else:
        print('PROGRAM: Huh?')

With this program, we can query the entries added to the database via the addrole.py script.

$ python3 getrole.py 
PROGRAM: What role would you like to know about?
who played thor
PROGRAM: Huh?
PROGRAM: What role would you like to know about?
tell me who played thor
chris hemsworth played thor
PROGRAM: What role would you like to know about?
tell me who played batman
christian bale played batman
PROGRAM: What role would you like to know about?
exit
PROGRAM: Bye!
$