Lab 7 - Python & SQLite

Please download the database file and place it in the folder z:\db. For your reference, the schema of the database is as follows:

   Actor ( actor_id , movie_id )
   Director ( director_id , movie_id )
   Movie ( id , name , year , rating , runtime , genre , earnings_rank )
   Oscar ( movie_id , person_id , type , year )
   Person ( id , name , dob , pob )

The steps needed to access SQLite from a Python program is the following:

>>> import sqlite3

>>> db = sqlite3.connect(name of database file)

EXAMPLE :  db = sqlite3.connect('z:\db\mydata.db')  # db is the database handle

>>> cursor = db.cursor()    # this is the cursor

>>> cursor.execute(command)   # replace "command" with SQL command string

EXAMPLE :  yr = '2002'

              query = '''SELECT M.name,M.year 
                         FROM Movie M, Person P 
                         WHERE M.id=P.id AND year = ?;''' 

           The above query is a parameterised query and can be executed as follows:

           cursor.execute(query, [yr])

>>> cursor.fetchone()

for tuple in cursor:
     #code to process tuple goes here

>>> db.rollback()

>>> db.commit()

>>> db.close()

1. Open a new module file called getMovies.py and include the steps stated above. Write a query to find the name and duration of all Steven Spielberg films. Print the results on the screen in the following format

    Jaws ... 124
    Raiders of the Lost Ark ... 115
    E.T. the Extra-Terrestrial ... 115
    Indiana Jones and the Temple of Doom ... 118
    Indiana Jones and the Last Crusade ... 127
    Jurassic Park ... 127
    Schindler's List ... 197
    Lost World: Jurassic Park, The ... 129
    Saving Private Ryan ... 170
    Minority Report ... 145
    Catch Me If You Can ... 141
    Indiana Jones and the Kingdom of the Crystal Skull ... 122
    War of the Worlds ... 116

2. Instead of printing the result directly on the screen, let's write the result to a txt file (such that we can do some further processing if needed). To write to a file, you may need to use following statements:

    outfile = open("z:/db/output.txt", 'w')
    outfile.write(tuple[0]+" ... "+str(tuple[1])+"\n")
    outfile.close()

4. Now we integrate No.2 & No.3 functionalities into the program: whenever the user enters the name of a director, we create a text file with that name, perform the query and write the result to the text file.

CS105
CS105 Labs