Accessing SQLite from a program
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_id , movie_id )
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:
for tuple in cursor:
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
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')
3. Now let's modify the program to allow the user to enter the name of the director and perform the query to find the name and duration of all films directed by the director. Use a parameterized query.
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.